Excel 97 into SQL varchar field

[tadpcf]

Microsoft SQL99 Server varchar field is not accepting data from Excel 97 Workbook. Gives NULL. Accepts 1917, not 191C or 191D. Tested by changing 9774 to 977A, gave me NULL, changed back to 9774, still gave me NULL! Workaround is intermediate text step, but would be time-costly.

tadpcf

 

[NathanGriffiths]

Hi,

I think this problem is caused by the Excel ISAM driver which converts the data from Excel to SQL Server.

When processing the data it looks at each column and makes a guess at the data type based on the first eight values in the column. If there are mixed numbers and text, it will go with the majority (either numbers or text) and values of a different data type will be imported as NULL.

see

http://support.microsoft.com/default.aspx?scid=kb;en-us;236605

The only way I know of to get around this is to enforce the text datatype in your spreadsheet. You will need to use the =TEXT() function to convert all the values in the column to text and 'Cut' and 'Paste Special..Values' to overwrite the original data in the column. Change the format of the column to Text.

All values in the column should then import to SQL Server as seen.

Hope that helps,

Nathan

 

[manufcs]

NathanGriffiths is right. I tried your problem as mentioned in

http://support.microsoft.com/default.aspx?scid=kb;en-us;236605

I converted excel file into text file and then using DTS migrated to SQL Server. All rows were successfully migrated.

Manu

 

[tadpcf]

To Nathan and Manu: Thanks for advice, you're on the right track I'm sure, as it's pretty clear SQL is making its decision based on early records. I apparently haven't yet been able to force the Excel spreadsheet to convert the column to text, or if I have, the conversion doesn't survive into SQL. It always seems to be successfully converted, but the NULLS remain. I'm working on it. (I find the Excel documentation for the Formula Palette very unhelpful.) Exporting the spreadsheet into text is of course one way to do it, and that works fine, but I'm still trying to save a step using the =TEXT() method. Is there a secret? Might it fail even if the conversion succeeds?

Thanks,
Tadpcf

 

[NathanGriffiths]

When I encountered a similar problem I resolved it using these steps;

1. Highlight the problem column in the spreadsheet and change the cell formatting to Text.

2. Insert a new blank column to the right of the original.

3. In the first cell of the new column use the TEXT() function to set the value to that of the corresponding cell in the original e.g. =TEXT(A1, "0&quot and drag this onto all the cells in the the new column. The the new column should now contain all the same values as the original, but with the data type set as Text.

4. Highlight the new column and select Copy and then highlight the original column and select 'Paste..Special' and then 'Values' from the context menu and this will replace all the values in the original column with with the same values but as Text.

5. Delete the new column

You may find this thread useful;

http://www.tek-tips.com/gviewthread.cfm/lev2/3/lev3/18/pid/68/qid/441373

Cheers,

Nathan

 

[tadpcf]

Nathan:

It worked! Thanks very much. (But now, of course, I have to finish the project!)

All the best,

Tad