July 29, 2020 at 12:21 am
Hi,
I first have to say that I am not that knowledgeable on file imports, but this is really beyond me.
My manager has a file that has adnominal spaces in it, I think some are tabbed and some just space, but my manager wants me to import the file into a table and keep the spaces just as they are.in the file.; because something that he does with the file after.
Anyway, I would like to get a script that I can put in a stored procedure, that would import this and keep the spaces.
Would anyone know where I can find this?
Thank you
July 29, 2020 at 3:22 pm
Can you provide an example of what any given line in this file looks like? Also, what, exactly, is an "adnominal space" in this context?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 29, 2020 at 3:54 pm
First thank you for the reply.
Second, I misspelled this, I meant abnormal. Unfortunately I am not able to post the exact data.
However, what my manager is saying is that if he uses SQL Server import wizard to import the data manually. He showed me that if he copies the rows to note pad, that he imported with the wizard it retains the spaces. However, when he uses our import app (this is a VB.Net app that uses BCP to import the data) it takes out the spaces. Because if he copies the rows that are imported with his app, into note pad, the spaces are changed.
For example, if we have 1245d BB1235689 which has there spaces in it, it will now have only one space as 1245d BB1235689.
Would the BCP process trim spaces?
Thank you
July 29, 2020 at 6:31 pm
It would depend on exactly how the BCP process was coded. Without any of the details on the destination table or even an example of 1 row of an import file, there's not much anyone can do but venture guesses. The details matter hugely on this. It shouldn't be difficult to import the entire line into a single column of a particular number of characters, so retaining the spaces can happen easily enough. You could then use that as a staging table for the data, with subsequent processing designed to ferret out any actual separate column values from the entire line by whatever rules might be needed to do so.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 29, 2020 at 7:09 pm
If the entire file is less than 2GB, I suggest BULK IMPORTing the file into a single CLOB. Then use DelimitedSplit8K to separate it into rows (assuming each row is 8K or less). If the row is more than 8K, naturally you'd need the MAX version of the delimiter function.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 29, 2020 at 7:35 pm
Okay thank you for your help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply