Replacing Chr(9) / Tab in Import File

[geoffcodd]

Hi,

Hopefully someone can help, I have a text file where some of the lines have a tab within them which is forcing the data on to a new row.

Is there a way with ay a batch file that I can remove these before importing the file.

Thanks in advance

 

[RuralGuy]

Are you saying that there should *never* be a tab in the input Text File?

 

[geoffcodd]

Are you saying that there should *never* be a tab in the input Text File?

Yes, no Tabs

Thanks
Geoff

 

[RuralGuy]

What version of Access are you using?

 

[bulrush]

In Access, right after you read each text line, before you process the line, replace all tabs with null:
s=replace(myline, chr(9), "")

Check in Access Help to see if this replaces only the first instance or all instances.

 

[RuralGuy]

It would depend on what method the OP is using to import the file. It is possible to do the import in code as bulrush suggests and strip out the Tabs yourself. How many fields are involved in the import? Can we see a sample of the imput?

 

[geoffcodd]

Here is a small example, the files can be anything from a couple of line too 1000's of lines.

I would prefer to clean file before import, if possible someone did mention about using a batch file to do this?

E|GERMANY|2103|SV01|SV01|DEA0207006|DEA0207006|DEA0207006|ashampoo GmbH & Co. KG|NONASP|NONASP|NONASP|200804|200804|W020009427|01/04/08|3|Mid-Markets|35|MM Direct|394|IP Services|431|Internet|439|Dedicated Access|I0212|IntDed 768K-2M CPE|0|0|0|EUR|100|0|EUR|100|0|154.23|0|W0H58167|ude190193|Internet Dedicated Standard Service|Oldenburg|DE|NA|NA|E1|Internet Dedicated Standard|NA|X|X|0|NA|IntDed 768K-2M CPE
.$Revision: 1.1 $|NA|E1|01/04/08|30/04/08
E|GERMANY|2103|IC02|IC02|DEC0242664|242664|0|RAINER KUNTZ ARAL TANKSTELLE|30AMOC|BP AMOCO PLC|30AMOC0001|200804|200803|3942066|01/04/08|1|PREMIER|44|Premier (A End)|63|Core Voice Services|193|Voice LD Services|274|Outbound International - Switched|I0425|Local Indirect Voice|0|42|97.97|EUR|1.266|0.012922323160151067|EUR|1.266|0.012922323160151067|1.9526|0.019930590997244053|NA|NA|NA|NA|NA|NA|NA|NA|NA|NA|X|X|NA|NA|NA|NA|NA|01/01/00|01/01/00

 

[RuralGuy]

Is there a maximum number of characters on a line?

 

[DCrake]

What you need to do is to use the following logic to strip out the tabs

Dim ffIn as Long
dim ffOut As Long

Dim StrString As String

ffIn = FreeFile
ffOut = FreeFile

Open "C:\TempIn.Txt" For Input As #ffIn
Open "C:\TempOut.Txt" For Output As #ffOut
   Do Until EOF(ffIn)
      Line Input #ffIn, StrString
      StrString = Replace(StrString,Chr(9),"")
      Print #ffOut, StrString
   Loop
Close #ffIn
Close #ffOut

Essentially it takes the incoming text file and opens it up and reads each row 1 line at a time. A replace command is used to strip out any tabs then save the revised line to a new file.

You would then use the new file to base your import upon.

Code untested for brevity only.

David