Excel Import SQL
I have a spreadsheet with about a dozen columns in it. When I run the SQL import wizard it imports all column except one. I have been starring at this all morning now trying to figure out why. The column contains actual values not formulas. I have noticed that for this one column, even though the values are all integers and I have formatted this column as text, the little triangle that appears in the upper left hand corner of the cell is missing. The indicates that the value of the cell is a number but it is being treated as text. Is this why this one column comes over as blanks? How do I get this entire column to be treated as text just like all the other columns so the SQL import manager will import the values?
ASKER
Where do you put this connection string? I may just manually type in these 300 numbers. I found that if I type in the number it puts the little triangle in there. I think part of this issue is that the first one hundreds rows are blank and then I get to the numbers I need.
Hi,
yes, that's the reason why the column is guessed as text. But if you have the numbers in the later rows the import of the data did work as expected, you said above that the column was not imported completely which is not the case. So you must check your SQL or the result data of your SQL first to see why the first x rows are empty, that's of course no issue of Excel. If you want to force Excel to format that as a number you need to add a row with the desired datatype as first row which can contain dummy data, like in this example:
The syntax depends on the used database.
It also depends where you enter the connection string which way you used to import the data. In case of Microsoft Query you'll find that in the properties of the connection, otherwise in the ODBC entry, whatever. You should be more exact if you want to get exact answers.
Cheers,
Christian
yes, that's the reason why the column is guessed as text. But if you have the numbers in the later rows the import of the data did work as expected, you said above that the column was not imported completely which is not the case. So you must check your SQL or the result data of your SQL first to see why the first x rows are empty, that's of course no issue of Excel. If you want to force Excel to format that as a number you need to add a row with the desired datatype as first row which can contain dummy data, like in this example:
SELECT 1 AS MyNumber, 'A' AS MyText
UNION ALL
SELECT MyNumber, MyText FROM MyTable WHERE....The syntax depends on the used database.
It also depends where you enter the connection string which way you used to import the data. In case of Microsoft Query you'll find that in the properties of the connection, otherwise in the ODBC entry, whatever. You should be more exact if you want to get exact answers.
Cheers,
Christian
ASKER
In my case, someone sent me this spreadsheet and all I need is to import the data into SQL Server. The data is already in Excel. I have tried formatting the column as text and copying column to column and the only thing that worked was manually typing in all those values. I am not pulling any data out of SQL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good point, maybe I will just dump this out to a CSV file and bring it in that way. I reread my initial post and you are correct. I should have worded it better.
ASKER
Thanks for your help!
You're welcome..glad if I could help you.
By the way, another easy and relatively fast way is to use a simple ADODB recordset, open the target table on the server and use a loop with AddNew. Has the advantage that there is no issue with data guessing as the datatypes are forced through the table fields. You only need to do that in a frontend script, means, VBA in Excel or VBScript using the Excel files or something like that.
Cheers,
Christian
By the way, another easy and relatively fast way is to use a simple ADODB recordset, open the target table on the server and use a loop with AddNew. Has the advantage that there is no issue with data guessing as the datatypes are forced through the table fields. You only need to do that in a frontend script, means, VBA in Excel or VBScript using the Excel files or something like that.
Cheers,
Christian
usually the driver has an automatism which guesses the datatype of the imported data using the first 8 rows of the data. This can be expanded up to the first 16 rows using some registry tricks, but that would be needed on every user's computer and 16 is also not really enough. The simpler way is to use a UNION ALL query where you add a row of text constants as first row, that forces Excel to use datatype text for all columns. Another way is to use the IMEX=1 property in the connection string, further informations can be found here:
http://www.connectionstrings.com/excel/
Cheers,
Christian