SQL Server 2005 import from Excel spreadsheet does not correctly import the numeric column names

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

(Note: you only need to change the format of the column name).

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

ged325:
Are you saying that I should go to the spreadsheet and manually change the column name?

dwe761: Yes

Yes, it looks like you've got the choice of changing the data type of all the cells on row 1 to text as ged suggested or changing the field names after the fact.

Seems would be easier to change your spreadsheet.

You could probably create a macro to do that for you so you wouldn't have to do that manually each time.

ASKER

I have already tried it but unfortunately it does not make any difference, same behaviour as mentioned in the original question.

I selected the first row, go to format menu, select cell and select Text option provided in the Number tab. Is there anything different you suggest?

If you precede each cell value having a numeric column name with a single quote, then it works.

ASKER

Do I need to do it in the Excel spreadsheet?

-- Do I need to do it in the Excel spreadsheet?
Yes. Try it there first to verify that it works. I tried it and it worked.

If you have to do this often to multiple files, you could probably create a macro to find all numeric values in the first column, and first change them to text and then add a leading single quote where necessary.

ASKER

The issue is that this file (in Excel format) is placed on a server drive on ad-hoc basis by another team and I generally do the import manually. There are more than 100 columns in it and more than 70% are numeric. I am not at all aware of how to write macros and am not sure how complex this macro would be. Secondly, if I have a macro written, do I have to run it on the file each time before I do the import?

Are there any other options available to handle this issue?

You could also save your Excel file as text and then import it into SQL Server from the text file rather than the Excel file. As follows:

Do File | Save As ...
Save As Type: Text (Tab Delimited) (*.txt)
Provide a different name for your file.

From SQL Manager, Open Import wizard.
Data Source: Flat File Source
Browse for your file name.txt and finish wizard.

It worked for me.

ASKER

I tried it but the import wizard fails with the following error, note I never got any error when importing directly from Excel file:

- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 82" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Column 82" (338)" failed because truncation occurred, and the truncation row disposition on "output column "Column 82" (338)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\skaleem\Desktop\RIM\Accessories Data\Compatibility\ABO-SP-00040-001.001 Compatibility Matrix_082610.txt" on data row 17.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - ABO-SP-00040-001_001 Compatibility Matrix_082610_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

What about a CSV?

ASKER

do you mean should I import it as CSV?

No, from the Excel file, choose Save As CSV.

A more complete answer would be save as CSV from Excel and then from SQL, Import file of type CSV.

If you still have trouble, it would be helpful if you could post a sample of your data if it is not of a sensitive nature. Maybe there's an issue in the data that is causing this error.

ASKER

unfortunately the data is sensitive and I can not post it. However, I will try the CSV option sometimes tommorrow

almander

Here are some other methods you may have better luck with..

http://support.microsoft.com/kb/321686

Maybe OPENROWSET will work better?

ASKER

I tried the open rowset, but I guess the import wizard uses the same method internally :-). The results are the same, i.e numeric columns are still incorrectly named as the cell numbers in excel sheet.

Getting back to my previous suggestion of exporting to text (or CSV), did you ever look at what is in column 82 that SQL is complaining about? Very long string? Image or other type of data that can't be converted to text?
Try copying only two rows (one row of column headers and one row of actual data) to a new Excel file and then try it.
Narrow it down to the row or cell that is giving SQL the problem and maybe you can go from there.

Also, when you got those errors on the import, did you also get any errors on the "Save As" to txt from Excel?

tconsidine

There are actually several situations where SQL cannot import a spreadsheet, or not correctly.

IMHO, the most reliable work-around is to import thru MS Access. That is, first import the spreadsheets to Access (Access almost NEVER fails). Then import from Access to SQL - le voila.

It sounds circuitous, but this almost always succeeds. Both for this case (numbers in the headers),
and also when you have missing or inconsistent data in a column and SQL might commit to one theory about the column, like being numeric, and then reject all records with strings. Access seems to survey the situation more fully before guessing what it is.

almander

Sorry for distraction, it was worth a shot...

tconsidine: makes an excellent point.

ASKER

I understand but that will involve more manual work than what I am currently doing, i.e, I have written a script that I run to rename all those numeric column names after I do the import into SQL Server:

Example:

--Some columns are wrongly named during the import process and need to be renamed to the columns in the
--the spreadsheet we utilized as a source to import from
DECLARE @ColumnExist INT
SET @ColumnExist=0
--Rename the column names that were incorrectly named during the import process from the CompatibilityMatrix sheet
SET @ColumnExist=dbo.fnColumnExists('1234', 'CompatibilityMatrix')
IF @ColumnExist=0
EXEC sp_rename 'CompatibilityMatrix.F11', '1234', 'COLUMN';
ELSE
PRINT 'Column 1234 already renamed'

I thought that I would have some way of tricking SQL Sever or change some settings when using the import wizard to correct this import anomaly. However, I guess we are only adding manual work here that exceeds what I am currently achieving through my script. The pain I have to take in the script is whenever an additional column is added to the spreadsheet, I have to go back and change my script.

One last way you could do it.

Keep your data as a CSV.

Write scripts to generate the schema of the tables BEFORE you import. Then do a bulk import from the csv's. This way you always have the appropriate column names and you're not checking for a system generated one.