May 3, 2011 at 9:10 pm
I've been trying to import a .csv file vie the import wizard and it simply will not work. I've gotten every other table (all in .csv format) successfully imported. This one, however, contains a field that is what I would call a "memo" field if it were coming from an Access database.
This field is coming from an actual database and there are literally no records that have a text length of over 500 characters. But every time I try to import the file, I get truncation errors despite the fact that I tell the wizard to ignore on fail.
I've tried assigning this field to varchar(max), nvarchar(max), varchar(you_name_the_size). The only data types I haven't tried are "blob" and "text()" (because I've read that Text will become obsolete in future versions of SQL Server).
So, I'm at a loss as to why it fails to import. I'm just looking for anyone's ideas as to why this might be failing. Anything obvious I might be missing that is causing the failure.
Thanks.
-Michael.
May 5, 2011 at 2:17 pm
Does every record fail?
May 5, 2011 at 7:22 pm
Jshahan,
Well, the table is created but no records are imported. But just you saying that made me look at the .CSV file very closely
and at the first row number where an error occurred. It appears that upon exporting the file from Access the "Memo" field does not always have a closing Double Quote.. separating it from the next fields.. time stamp/date and user inserting.
Man, this will be a difficult clean up.. but now that I look at it, I'm sure this is what's wrong.
Thank you for answering.
-Michael.
May 6, 2011 at 9:13 am
Maybe when you export from access to CSV, you could concatenate a close quote..
SELECT someMemoField + '"', nextField FROM MyAccessTable
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 6, 2011 at 10:07 am
That's a good idea. You'll need to make it conditional though (a case statement that would search for the presence/absence of the quote), if the quotes aren't missing from every record.
May 9, 2011 at 3:23 am
hmmm good point
That would have to be done like:
SELECT '"' + REPLACE(someMemoField, '"','') + '"' ...
Manually remove any quotes from the field then add them back in manually?
I dont use access so not sure of the best solution here.
It may just be that the memo field itself has some double quotes in some records, causing the csv field to be 'close-quoted' prematurely. In which case you may just have to use REPLACE(someMemoField, '"' ,'''') to replace a double quote with a single.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 11, 2011 at 7:46 pm
Well, Guys..
I appreciate the responses but I think it's going to take a really custom solution or (more likely) human intervention.
This is, of course, a variable length field. There were extraneous double quotes in the 'memo' field, which I removed doing a simple Find and Replace in a text editor. But the text in this field also has extraneous commas, since it is a free-form field.
Access does not do a pretty job of exporting this table. After cleaning it up manually (half an hour or so) I can re-open it in Excel and all looks good. All the fields are in place. I thought importing the table as an Excel file would do the trick but the same truncation problem persists.
Long story short, it's no big deal at the moment. This is a replica of our database at work and I've got enough tables imported correctly to play with. This is a test database for me to practice on. I'm very new to T-SQL and I've already got another stupid problem I can't figure out, and I'll be posting a question about that in the Newbie forum.
Again, Thanks for your input.
-Michael.
May 16, 2011 at 4:21 am
Why don't you do it in 2 hops ...
export the data EXCLUDING the memo field - import that ...
then connect to the SQL server in access and update the records with the Memo field ..
Actually - why are you doing it via CSV anyway?
Why not just create the table in SQL. Open the Access DB - Link the table using ODBC and import all the data that way - DB to DB ??
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply