Error importing a simple txt file into SQL
-
Hi there,
I have a csv/txt file that I'm trying to import into SQL using DTS. It has some of it's text fields with "" and some without. I would like to import it direct into SQL with having to maintain the file first.
When I try to do this I get the following message
Error source: Microsoft DTS Flat file rowset provider
Error Description: Invalid delimited data: text qualifier must be following by a column delimiter (except the last column)
Context: Error calling OpenRowset on the provider
I'm not sure where to start looking so any pointers would be a great help.
Thanks
S
-
Whatever your text qualifier is, you have that in your data.
If your text qualifier as " (Quote) one of your fields has actual data with "Quotes" in it.
-
Ray,
I have left the text qualifier as "" - which is the default, but that is when it gives me that error.
-
If you have a mix of qualified and non-qualified text fields, you are probably going to have to do some sort of data scrubbing either before or after import.
You could import with the text qulaifier set to "none", which would leave some of your database column values containing double quotes.
A post-import scrub operation would consist of UPDATE's like this:
-- Strip leading & trailing double quotes off column values
UPDATE YourTable
SET ColumnValue = Substring(ColumnValue, 2, DataLength(ColumnValue) - 2)
WHERE ColumnValue LIKE '"%"'
-
PW..thanks for that. I was hoping SQL would do that for me. But I guess not. XL does it automatically. I didn't really want to import it with no qualifiers because if there is a ',' in a text field it will see it as a new column eg. "LASTNAME, FIRSTNAME"
Any ideas?
-
If that does happen, then you can use ActiveX to rejoin the fields:
DTSDestination("FullName") = DTSSource("LASTNAME") & ", " & DTSSource("FIRSTNAME")
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply