June 18, 2008 at 1:45 pm
I have a file that has Integer and string within the same column. Depending which data type is first the second get nulled out. For example
col1 col2 col3 col4 col5
___________________________________________
123 joe bob 12 abc
abc alex goat abc efg
345 123 dog cat hij
345 wer 123 1234 234
After import into SQL, I get
col1 col2 col3 col4 col5
___________________________________________
123 joe bob 12 abc
NULL alex goat NULL efg
345 NULL dog NULL hij
345 wer NULL 1234 NULL
Does anyone know how to fix this? Any help will be appreciated
thanks
June 18, 2008 at 2:06 pm
How are you importing this into SQL?
Matt
June 18, 2008 at 2:10 pm
SELECT *
FROM OPENROWSET ('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;
Extensions=csv;',
'SELECT * FROM myFile.csv')
Same problem occurs with XLS file and with import wizard as well
June 18, 2008 at 2:16 pm
I haven't seen numbers disappear out of text columns, but I have seen text disappear out of number columns, in that same manner.
My solution was to pre-define the table I was importing into, make sure the columns were all varchar, then clean up the data as needed and finally move that into a table with correctly defined data types on columns.
Another solution I've seen work is change the file type to .txt and save it fixed-width instead of delimited.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 2:30 pm
still wont work 🙁
June 18, 2008 at 2:41 pm
The problem is the import process takes a look at a small sample of the data in the column and makes a judgement as to what the data type is. This behavior also happens in a column intermixed data, meaning alphanumeric.
Try specifiying imex = 1 to your import. I would also recommend you use a format file, as this will more than likely solve your problem, and that you specify the bulk option for openrowset if you plan on importing bulk data.
Your connection string should look like this to excel.
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=NO;IMEX=1;Database=C:\test.xls;',
'SELECT * FROM [sheet1$]')
June 18, 2008 at 3:08 pm
thanks Adam, you are a genious
June 18, 2008 at 9:35 pm
I got a new problem...how do i import a pipe delimiter file. This is what I try but it's not working
--Example to open CSV file
SELECT *
FROM OPENROWSET ('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};HDR=NO;IMEX=1;
DefaultDir=C:\;
Extensions=csv;Delimiter=|',
'SELECT * FROM myFile.txt')
but it's not working
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply