March 18, 2009 at 6:48 pm
Hello Gurus,
I have a text file formatted as following:
"OU00000004""SGC""Department ""001""Domestic Banking ""Domestic Banking ""OU20002138"
"OU00000005""SGC""Department ""001""Personal Banking & Distribution ""Pers Bkg & Dist ""OU20002138"
.......
Can someone tell me how to import them into SQL 2005? e.g. How to let SQL know how each column is defined?
Thanks lots.
March 19, 2009 at 1:14 am
You can start with reading the BOL section about bcp.
March 19, 2009 at 1:58 am
Hello,
You can use the SQL Server Import Wizard and select the Flat File as the Source.
The Wizard allows you to store the settings that you select as an SSIS Package for re-use.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
March 19, 2009 at 7:45 am
Thanks guys,
Just can't get the columns corrected columnized. I mean, what seperator should I use in this case?
March 19, 2009 at 7:49 am
Based on your sample data, wouldn't you use quote separated or am I missing something? Using the import wizard, I believe it's the first or second screen after you select your source file where it asks for the delimiter. Make the selection and select preview so you can verify it's what you're expecting.
-- You can't be late until you show up.
March 19, 2009 at 7:53 am
tosscrosby (3/19/2009)
Based on your sample data, wouldn't you use quote separated or am I missing something? Using the import wizard, I believe it's the first or second screen after you select your source file where it asks for the delimiter. Make the selection and select preview so you can verify it's what you're expecting.
As I can see from the sample data, the quote seems rather the "text qualifier"...
Halifaxdal, can you tell which separator was used when creating the text file?
March 19, 2009 at 7:58 am
dmoldovan (3/19/2009)
As I can see from the sample data, the quote seems rather the "text qualifier"...Halifaxdal, can you tell which separator was used when creating the text file?
I realized that after I posted. I then tried to delete my post but you had already responded. I have a string parser script at work. When I get in, I'll find it and post it. Essentially, if you import the entire string, it will parse through it extracting the data based on whatever "delimiter" you pass in. I think I actually found the script on this site and modified it slightly for myself.
-- You can't be late until you show up.
March 19, 2009 at 10:37 am
I used " (double quote), which you can see in the text file, as the column delimiter, the problem is it creates empty column for every real column for me.
As to my sample data, that is:
Column 0: nothing
Column 1: OU00000004
Column 2: nothing
Column 3: SGC
and so on and so forth.
March 19, 2009 at 12:00 pm
halifaxdal (3/19/2009)
I used " (double quote), which you can see in the text file, as the column delimiter, the problem is it creates empty column for every real column for me.
Can you re-create the file using for example a comma as separator?
March 19, 2009 at 12:12 pm
No, I don't have control on the data source.
March 19, 2009 at 12:42 pm
The funniest thing I saw here is SQL doesn't seem to handle this properly:
Here is my procedure in the import/export wizard:
1. Choose Flat file source
2. Set Column delimiter as " (double quote), in the preview rows 1-100, it shows:
Column 0: nothing
Column 1: OU00000004
Column 2: nothing
Column 3: SGC
...
So, I go to "Advanced" menu in the left pane of the wizard, I see 19 columns, for all the even number of columns, I want to delete them, i.e. Column 0, 2, 4, ....
But, no matter how I delete it, I still see it in the preview window!
March 19, 2009 at 12:45 pm
Try to skip the empty columns using a format file with bcp, or creating a SSIS package and editing the column mappings.
Another solution may be creating a string parser - please see our previous posts...
March 19, 2009 at 12:49 pm
This simple job is driving me nut, can you provide any instruction on this particular case? I never use bcp before.
Thanks lots.
March 19, 2009 at 12:59 pm
halifaxdal (3/19/2009)
This simple job is driving me nut, can you provide any instruction on this particular case? I never use bcp before.Thanks lots.
This is a rather long story, and BOL "tells" it in much more detail than I could do in a forum post...
The same for the SSIS packages.
You can also create a .NET simple application which parses the file and imports it in your database.
As you see, using the double quote as a delimiter is not a good idea...
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply