1

I have a program that writes to a text file with the "\n" character. I have it write this to a .csv file so that I can then take that information and import it into an Access database. The issue that I am running into is that Microsoft Access VBA and Notepad are not recognizing the line breaks within the file. Wordpad, Access's built in Import, and Excel DO recognize the line breaks. If I open the .csv file in Excel or WordPad, make no changes, and save the file as a .csv file again, then it will show up right.

The file opened in Notepad

The file opened in Excel

The file importing in Access

I have a VBA script to import the data from the file, since data is repeatedly added to a master sheet. I want it to take each line from a .csv file and either insert it as a new row if the ID isn't already present OR update the existing row if there is one. This is tested and working for file where they are blatantly on different rows (showing in notepad as different rows), but it is just the files generated by my program (using \n as new line).

The other program is built in C# ... So, i guess my question is: is there is a better line break character or sequence to use in C# to make it more universally recognized when a line break is present?

For posterity, here is the code that my VBA script is using that is reading the file as one line and missing the line breaks:

'Now read the file
Open csvLoc For Input As #1
Do While Not EOF(1)
    Line Input #1, csvLine
    Debug.Print csvLine
    lineArray = Split(csvLine, ",")
    <-- Code handling the now split line array -->
Loop
Close #1
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Samuel Ohrenberg
  • 61
  • 1
  • 2
  • 7

2 Answers2

0

Newline Control Characters:

  • \n = "Line Feed" (LF) = ASCII Code 10 = VBA Constant: vbLf

  • \r = "Carriage Return" (CR) = ASCII Code 13 = VBA Constant: vbCr

or, Windows prefers a combination of both:

  • \r\n = "CR + LF" = ASCII Codes 13 + 10 = VBA Constant: vbCrLf

Since you have existing LF's, there are a number of simple ways to change them to CRLF's. I can't think of any way that the additional character would be a problem.

The best plan would be to change the source application that's creating the files, so use both characters instead of one.

If your main concern is the data that you're importing with VBA, you could make a small change to your code, to replace the characters:

Open csvLoc For Input As #1
Do While Not EOF(1)
    Line Input #1, csvLine
    csvLine = Replace(csvLine, vbLf, vbCrLf)
    Debug.Print csvLine
    lineArray = Split(csvLine, ",")
    <-- Code handling the now split line array -->
Loop
Close #1

More Information:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
0

You do not mention having an Access link to the CSV file.

If Access' built in import recognizes the line breaks natively - then relying on a linked method would be easier than a vba based import.

Cahaba Data
  • 624
  • 1
  • 4
  • 4