locked
Exporting data help RRS feed

  • Question

  • My program saves text from a text box to a text file, the text file is then imported into MicroSoft Access. My poblem is that when Access imports a text file it assigns a row to each line if text.

    So I would like to deliminate each line of text with a "~" so it can be process in Access as a column not a row.

    I can do this for small paragraphs of text when the number of text lines is known, here is my code used for reading vendor labels;

      SpecVendorSource1 = File.ReadLine (VPath + "\Vendors\" + SpecItemVendor  + " LABEL.txt", 1 )
      SpecVendorSource2 = File.ReadLine (VPath + "\Vendors\" + SpecItemVendor  + " LABEL.txt", 2)
      SpecVendorSource3 = File.ReadLine (VPath + "\Vendors\" + SpecItemVendor  + " LABEL.txt", 3)
      SpecVendorSource4 = File.ReadLine (VPath + "\Vendors\" + SpecItemVendor  + " LABEL.txt", 4)
      SpecVendorSource5 = File.ReadLine (VPath + "\Vendors\" + SpecItemVendor  + " LABEL.txt", 5)
      SpecVendorSource = SpecVendorSource1 + "~" + SpecVendorSource2 + "~"+ SpecVendorSource3 + "~"+ SpecVendorSource4 + "~"+ SpecVendorSource5 + "~"

    My question is; Is there a way that I can join all the text lines in a paragraph as one variable, with the deliminators (as shown on the last line above) when the length of the paragraph is unknown.  i think a while loop will work but I'm not experienced enough to get it to work.

    If your still reading this then your help is greatly appreciated,

    thanks,

    steve

     

    Friday, August 19, 2011 12:03 AM

Answers

  • I don't really know what Microsoft Access is, so I'll just go on what you said. First, you need to get the number of lines in the text file. If you already know how many lines it is, that would help and make this a lot faster. Next, you need to loop until the number of lines, then use Text.Append (do not use variable1+variable2 because if they happen to be numbers, they will add together, not append) to append "~" to the line. Then last, you need to just write the output to the specified filepath.

    filepath = "C:\test.txt"

    TextWindow.Writeline("Enter the number of lines in your file:")

    lines = TextWindow.ReadNumber()

    For i = 1 to lines

      sourceline=File.ReadLine(filepath, i)

      output = Text.Append(output, Text.Append(sourceline, "~"))

      i=i+1

    EndFor

    File.WriteContents(filepath, output)

    Does that help?


    ~~AirWaves!!~~
    • Marked as answer by SFC-P33A Friday, August 19, 2011 3:56 PM
    Friday, August 19, 2011 12:17 AM

All replies

  • I don't really know what Microsoft Access is, so I'll just go on what you said. First, you need to get the number of lines in the text file. If you already know how many lines it is, that would help and make this a lot faster. Next, you need to loop until the number of lines, then use Text.Append (do not use variable1+variable2 because if they happen to be numbers, they will add together, not append) to append "~" to the line. Then last, you need to just write the output to the specified filepath.

    filepath = "C:\test.txt"

    TextWindow.Writeline("Enter the number of lines in your file:")

    lines = TextWindow.ReadNumber()

    For i = 1 to lines

      sourceline=File.ReadLine(filepath, i)

      output = Text.Append(output, Text.Append(sourceline, "~"))

      i=i+1

    EndFor

    File.WriteContents(filepath, output)

    Does that help?


    ~~AirWaves!!~~
    • Marked as answer by SFC-P33A Friday, August 19, 2011 3:56 PM
    Friday, August 19, 2011 12:17 AM
  • I would look at the way that you write the data.  Put all your varaibles into one record.  Seperate each variable with a marker or delimiter such as a comma.  If you use a comma to separate the data, then use the file extension .csv (Comma Seperated Values)  Most Microsoft programs will automatically separate the values when read.  If the first line contains the variable names, they will be used.

     


    Bob Jones
    Friday, August 19, 2011 1:52 AM
  • Thanks AirWaves,

    It gives me something to work with!

    Friday, August 19, 2011 3:54 PM
  • thanks Bob,

    I'll give it a try, I use the "~" instead of a comma because it some of the text that is read contains a comma, it is interpeted as another column in Access.

    Friday, August 19, 2011 3:56 PM