CSV import

[SFC]

I created a report that import data from a text file, the text file has multiple lines of text. When Access imports the data it treats each line of text as a seperate row, even though the field type is "Memo". This screws up my report because Access creates a seperate sheet for each line of text.

Is there a way to import multiline text as one piece of data?

Any input is appreciated.

Thanks,
Steve

[ajetrumpet]

have you tried tagging the end of each piece of data with a delimiter char, like a pipe for instance ( | )?? Try doing it, then specifying the pipe as the delim when you use the import wizard.

IDK if that fixes the wizard's default interpretation of reading each LINE as a seperate record, but its worth a shot.

[SFC]

Thank Adam,
Although that would work, the text file is generated by someone typing the information, the person typing the information would have to type the delamination, and who knows were the end of line would be?

[ajetrumpet]

I doubt that the wizard can interpret this any other way than one line of text per record.

and actually, I think the delimiter concept relates to fields only, not rows. Sorry!

Bottom line - you need a way to detect carriage returns in your text file. That's the problem, right? If the user typing directly into the text file? Why are they pushing return? Why can't they just type it out into a long line of text or a paragraph?

FYI - if they are pushing return in a text file so they don't have to scroll horizontally to see words, tell them to turn the word wrap on.

[SFC]

Thanks ajetrumpet,
Yes, it would work if the user did not use returns put sometimes the imput requires it.
For example,
Color: red
Heght: 36"

[ajetrumpet]

Thanks ajetrumpet,
Yes, it would work if the user did not use returns put sometimes the imput requires it.
For example,
Color: red
Heght: 36"

From what I can see, there is nothing you can do from an Access standpoint. Here's as close as I can think of ---- run this code on your text file:

Function ReadFile()

dim fso
dim ofile
dim oContents As string

   Set fso = CreateObject("Scripting.FileSystemObject")
   Set ofile = fso.OpenTextFile("PATH OF FILE", 1)
   oContents = ofile.read(111111)
   ofile.Close

   Debug.Print Replace(oContents, vbCrLf, "VBCRLF")

     Set fso = Nothing
     Set ofile = Nothing

End Function

Notice that the result shows you where the line feeds are. These can be interpreted by Visual Basic regardless of how they got into the text file. But how can you know which ones to keep and which ones to toss. If you could figure that out, you could import the file through code and use the relevant "vbcrlf" to differentiate between record breaks. Furthermore, if you file looked something like this:

Color: red
Heght: 36"


Color: red
Heght: 36"


Color: red
Heght: 36"

all you'd have to do is detect the double line breaks and use code to import it accordingly.

[SFC]

Thanks again, I'll give it a try.