Help importing text files with line breaks

[LittleGlory]

Hi, I am hoping someone can give me some help with a problem. I am currently trying to import text files into Access. I have done this many times and haven't had a problem. However, I am stuck on this issue. It is probably simple to solve but I can't figure it out.

I am trying to import a very large text file. The data is comma delimited. The problem is that program that is exporting the text file (a machine that takes measurements of certain things) exports the text file with line breaks in the middle of the data. For example, the file shows a serial number of an engine followed by many different peices of measurement data. There are several breaks in the data that move to the next line. So, I have what should be imported as one record in Access, on 4 different lines in the text file. Access looks at each line as a separate record. I am wondering if there is an easy way for me to import this data all as one record without going into the text file and putting it on one line. Below is a condensed example of the file.

Serial Number3C4597,6.5,7,8,7.8,9.1,
5.4,16.7,
8,4,5,9,10,11,12.5,
19.2,6,7,12

Serial Number3Df567,14,15,6,5,7.8
5,7,9,
4,5,6,7,89,45,15,13.2,
45.2,2,6,13

Each group of 4 lines should be one record, however access assumes that each line is a separate record. Any help would be appreciated!

 

[WayneRyan]

LittleGlory,

Something like this should work for you.

Dim strOneLine As String
Dim strNewLine As String

Open "C:\YourFile.txt" For Input As #1
Open "C:\NewFile.txt" For Output As #2

While Not EOF(1)
   strNewLine = ""
   Line Input #1, strOneLine
   strNewLine = strNewLine & strOneLine
   Line Input #1, strOneLine
   strNewLine = strNewLine & strOneLine
   Line Input #1, strOneLine
   strNewLine = strNewLine & strOneLine
   Line Input #1, strOneLine
   strNewLine = strNewLine & strOneLine
   Print #2, strNewLine
   Wend

Close #1
Close #2

Wayne

 

[crimsonaire]

Hi Wayne, I came across this thread thru google. Your solution taught me how to remove the line breaks, thanks!
However I need to import text files consisting of a different number of lines each.. how do I write a loop that will go thru each line and remove the line break? Any help is much appreciated!

 

[workmad3]

Is there a blank line seperating each record? If so, then

Dim strOneLine As String
Dim strNewLine As String

Open "C:\YourFile.txt" For Input As #1
Open "C:\NewFile.txt" For Output As #2

While Not EOF(1)
   strNewLine = ""
   Line Input #1, strOneLine
   if strOneLine = "" Then
      Print #2, strNewLine
   else
      strNewLine = strNewLine & strOneLine
   end if
   Wend

Close #1
Close #2

should do the trick.

If there is no way of seperating the different records, then you may need to manually edit the file yourself

 

[crimsonaire]

Thanks for replying! I tried your approach but it didn't work.. it just cleared the contents of my text file.

Maybe I should elaborate more. I have text files with different number of lines in them. These are examples of my text files:

Text file 1:
The quick
brown
fox.

Text file 2:
Hello how
are you today?

Each text file only has 1 record. I need to remove the line breaks in each file. Meaning in e.g. 1 I need it to read "The quick brown fox." so that when I do a TransferText, the whole file's contents will be transfered to 1 memo column in the access table.

 

[WayneRyan]

crimsonaire,

Hope this gets you started.

Dim rst As DAO.Recordset
Dim strMemo As String
Dim strBuf As String
Dim strFileName As String
'
' Open a recordset to put your text files into.
' Your table (YourTable) has a field for the filename and
' a field for the memo text.
'
Set rst = CurrentDb.OpenRecordset("Select * From YourTable")
'
' Loop through all .txt files is some directory.
'
strFileName = Dir("C:\SomeDir\*.txt")
While strFileName <> ""
   ' For each file, build the memo string.
   Open strFileName For Input As #1
   Line Input #1, strMemo
   While Not EOF(1)
      strMemo = strMemo & strBuf  ' <-- Add "& vbCrLf" to retain line identity or add --> & " " to keep words from running on
      Line Input #1, strBuf
      Wend
   Close #1
   ' Add the new record to YourTable
   rst.AddNew
   rst!FileName = strFileName
   rst!MemoField = strMemo
   rst.Update
   strFileName = Dir()
   Wend

hth,
Wayne

 

[crimsonaire]

It works perfectly! Thank you so much for your help!!