Solved

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

Posted on 2010-08-26
25
519 Views
Last Modified: 2012-05-10
I have tried importing data from a spreadsheet to SQL Server table utilizing the standard import wizard provided in SQL Server 2005. The spreadsheet has multiple numeric column names such as 123, 145, 147 etc. The import process incorrectly names those columns as someting like F1, F2, F3 etc (depending upon the column in the spreadsheet) in the resulting imported table. Is there a way to instruct the import process to correct this issue or any workaround? This is annoying as I have to manually change those column names after every import.
0
Comment
Question by:skaleem1
  • 10
  • 8
  • 4
  • +2
25 Comments
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 250 total points
ID: 33534404
Try changing the column from numeric to text.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 33534413
(Note: you only need to change the format of the column name).
0
 
LVL 10

Assisted Solution

by:dwe761
dwe761 earned 250 total points
ID: 33534439
On the import wizard, do you have the checkbox checked that says "First Row has Column Names"?
0
 
LVL 1

Author Comment

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

dwe761: Yes
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33534880
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.
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33534956
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?
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33535049
If you precede each cell value having a numeric column name with a single quote, then it works.
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33535069
Do I need to do it in the Excel spreadsheet?
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33535229
-- 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.
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33535471
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?
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33535668
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.
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33535836
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)
 

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 33536013
What about a CSV?
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33536499
do you mean should I import it as CSV?
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33536520
No, from the Excel file, choose Save As CSV.
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33536599
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.
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33538217
unfortunately the data is sensitive and I can not post it. However, I will try the CSV option sometimes tommorrow
0
 
LVL 5

Expert Comment

by:almander
ID: 33538938
Here are some other methods you may have better luck with..

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

Maybe OPENROWSET will work better?
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33543517
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.
0
 
LVL 10

Expert Comment

by:dwe761
ID: 33543586
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?
0
 
LVL 1

Expert Comment

by:tconsidine
ID: 33547085
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.
0
 
LVL 5

Expert Comment

by:almander
ID: 33547113
Sorry for distraction, it was worth a shot...

tconsidine: makes an excellent point.
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33560387
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.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 33576812
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.
0
 
LVL 1

Author Closing Comment

by:skaleem1
ID: 33577166
Again as the spreadsheet is continuously changing and new columns are being added on weekly bases, in that case I will have to manually make those changes. The amount of work this involves is not less than the amount of work I am currently doing by keeping the scripts updated as I mentioned in my last post. What I originally looking was to make some changes in the SQL Server import wizard setting to somehow correct the imported table. But seems like this is not an option. However, I will keep researching on this.

I appreciate all your help and will refund you the points as you replies implicitly prove that there is no viable option to reduce the manual amount of work. I will split the points between ged325 and ged325. Thanks anyways for everyone who spent time to  help.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now