text file import header problem

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 "=").

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

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?

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

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.

Click to expand...

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.

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

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.

Cheers Joe,

this method was fine

regards

john