text file import header problem

john.battye

Board Regular
Joined
Jul 18, 2008
Messages
58
I am importing a text file into access. I have to import it as 'fixed width' format. This does not give me the option of selecting the 'first row as headers', therefore the headers are field1, field2 etc and the actual headers from the text file import as the first data row.

Is there a way round this?, a quick way to change the data row to be the header row, or an update/append query solution.

Any help much appreciated.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,958
Office Version
  1. 365
Platform
  1. Windows
Sometimes, I will use a Select Query to filter them (like putting criteria in there like:

<>"Field1"

Similarly, you could create a Delete Query to actually delete this row, if you like (obviously change your criteria from "<>" to "=").
 

john.battye

Board Regular
Joined
Jul 18, 2008
Messages
58
thanks for the reply joe, but I'm not sure its helps me.

<TABLE style="WIDTH: 239pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=319 border=0 x:str><COLGROUP><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=63 height=17>Field 1</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=87>Field 2</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=86>Field 3</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=83>Field 4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>name</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">contact no.</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">address</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">postcode</TD></TR></TBODY></TABLE>

After my import I am left with this table. I want to change the field headers to the actual ones. For this example I could do it manually in a minute but my real data has 56 fields. Is there a way I can do this?

regards
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,958
Office Version
  1. 365
Platform
  1. Windows
I apologize, I misunderstood your initial question.

You should be able to select the first row as Field Header names. How exactly are you importing this data? Are you using the Wizard or some Macro?

Which version of Access are you using?
 

john.battye

Board Regular
Joined
Jul 18, 2008
Messages
58

ADVERTISEMENT

I am importing a notepad text file using the wizard in 2003. I get the option to choose 'first row conatins field names' when the format is 'delimited' but not when the format is 'fixed width'

regards

john
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,958
Office Version
  1. 365
Platform
  1. Windows
I guess I have been working with mostly delimited text files.

I did some research and came across this article:
http://office.microsoft.com/en-us/access/HA012322271033.aspx

Note what it says in the "Field Names" sections:
When you import fixed-width text files, Access does not give you the option of using the values in the first row as the field name.

Not sure why Microsoft Designed it that way, but apparently, that is the way it is.

A few workaround options include:
1. Converting it to a delimited file before import (could use Excel to do that)
2. Import into a "temporary" table, and use an Append Query to write the data from this temp table to your Permanent Table (where you have already pre-defined all field names - you would just need to set this up one-time). You can use the logic I discussed in my first response to filter out the header row that is imported as data.
 

john.battye

Board Regular
Joined
Jul 18, 2008
Messages
58

ADVERTISEMENT

thanks for the reply again Joe,

I have excel 2003 and there are >65000 rows. I have/am trying the second workaround (creating a blank table with correct field name and appending, but I can't work out how the append query should go.

regards

john
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,958
Office Version
  1. 365
Platform
  1. Windows
John,

I actually came across another workaround, which is even easier (as I mentioned, I don't work with Fixed Width Files much anymore, so I am working through this and seeing what options are presented).

Set up your "permanent" table with the correct field names.
Now when you import your Fixed Width File, there is an option to import into an Existing Table. Select your permanent table name. Then your data goes exactly where you want without the temporary table.

You'll just need to delete the false "header/data" row afterwards. To do this, simply create a query that selects all records where your first field equals "Field1". Then simply change the query type from Select query to Delete query and run.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,079
Messages
5,599,636
Members
414,326
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top