Solved

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

Posted on 2010-08-26
25
528 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 4
  • +2
25 Comments
 
LVL 40

Accepted Solution

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

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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
 
LVL 40

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 40

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Time out only from one specific computer client 15 53
Search Text in Views 2 28
What does "Between" mean? 6 47
ErrorKind in crystal reprot VB.Net 1 15
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 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