October 20, 2015 at 7:33 am
Hi,
I have imported a txtfile to access and have some problem making the data appear in my wanted way. I want to shift the cells in the first row one step to the left, i.e to overwrite the zeros with the left value. Then I want to assign the first row values as field names. I am new to sql and gettin really frustrated over this. Thanks
Current data
Field1 Field2 Field3 Field4 Field5
0 Name Major Sector Hi
karl
per
anna
Wanted outcome
Name Major Sector Hi
karl
per
anna
/ Johan:-)
October 20, 2015 at 7:41 am
j.sjoelund (10/20/2015)
Hi,I have imported a txtfile to access and have some problem making the data appear in my wanted way. I want to shift the cells in the first row one step to the left, i.e to overwrite the zeros with the left value. Then I want to assign the first row values as field names. I am new to sql and gettin really frustrated over this. Thanks
Current data
Field1 Field2 Field3 Field4 Field5
0 Name Major Sector Hi
karl
per
anna
Wanted outcome
Name Major Sector Hi
karl
per
anna
/ Johan:-)
How have you imported the text file? bulk insert? bcp? ssis? import/export wizard?
The main issue is that you need to correct the import if this will be a recurring issue. If it's a one time thing, my suggestion is to create a new table with the appropriate column names and types and insert the data correctly.
For more help, read this: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 20, 2015 at 7:46 am
Thanks for the reply. I used the import wizard to import the txtfile. I will need to import the txtfile daily so it will sadly be a recurring issue.
October 20, 2015 at 7:51 am
In that case, I'd suggest you drop the table and go through the import/export wizard again and make sure that you get the import definition correct (select 'column names in first row' and use the preview to ensure that the resultant data looks correct). Then you can save the package at the end of the wizard and schedule it for future loads. You don't want to be doing cleanup every day due to a problem that can be fixed once.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2015 at 8:17 am
Also did you indicate "Column names in the first data row"?
October 20, 2015 at 8:23 am
Sorry, for the poor quality of the post. The problem is that the first row of the import (txtfile) is data that I dont want to keep, so I cant set the first row as column headers in the import wizard. Now I imported the file using the wizard and then deleted the first row, but I have no idea how to progress from there, i.e to shift the second row one step to the left and to set the second row as column headers . I appreciate your help a lot hopefully I can contribute in the future.
October 20, 2015 at 8:27 am
You could bring the data into a temporary table then move to the correct position within a permanent table
Some example data and table definitions would be of help here as the example just has one column.
October 20, 2015 at 8:39 am
Thank you I managed to solve it in this way. Keeping one table for the import and then move the wanted data into a new table where I created the field names before. 🙂
October 20, 2015 at 9:55 am
j.sjoelund (10/20/2015)
The problem is that the first row of the import (txtfile) is data that I dont want to keep, so I cant set the first row as column headers in the import wizard.
You can.
First make sure that the check box is not ticked. There's a text box for 'rows to skip'. Set that to 1. Click preview, click back to the column settings. Now select the 'first row as column headers' option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply