locked
Importing csv created by dotnet Streamwriter into Access table RRS feed

  • Question

  • I'll start with the Access forum.  If necessary, I'll move this to the Visual Studio forum.  Since 2017 a function in an Visual Studio vb.net application has run faithfully several times a day and exports data to a csv format and puts the csv on an FTP site.  An Access database downloads the csv and appends the data to a linked SQL table using TransferText with an import specification.  

    The VS app uses the Excel interop programming.  I've been asked to modify this and use StreamWriter instead.  I have created the csv with StreamWriter but the TransferText in Access does not seem to recognize the format of the csv.  An error displays that the value being stored violates the settings defined for the table being appended to.  I cannot visually see any difference between the Excel-created csv and the StreamWriter-created csv.  Same columns, same rows.  Data appears to be identical.

    I removed the import specification but continue to get the error.

    By chance has anyone else had this problem and how did you solve it?  I will post code if necessary.

    Thank you.

    Tuesday, May 5, 2020 3:10 PM

Answers

  • If I try to import the csv into Access, it separates AAA and BBB into two columns, which is not the desired result.  I don't know how to get around that.

    Hi em_rdh,

    This is one of the many reasons why I use my own input routine, for far better control of what can happen.

    In the case that text-field contain comma's I use the following logic:

    - read the file line by line
    - split the line in an array using a comma as separator
    - loop through the elements of the array and write the content in the appropriate field, but …

    - when an element starts with a doublequote and has no endqoute, concatenate the element with the next element using a comma inbetween
    - when the next element has an ending doublequote the concatenated string is written in the field, else take again the next element, concatenate, etc.

    You should be able to do that too.

    Imb.

    • Marked as answer by em_rdh Tuesday, May 5, 2020 5:31 PM
    Tuesday, May 5, 2020 5:01 PM

All replies

  • Hi em_rdh,

    Instead of using import specifications etc, I directly read the csv-files line by line, and do further processing.

    Recently I got an error when processing an other csv-file. The difference was that in "normal" a Chr(13)Chr(10) line separator was used, and in this special case only Chr(10) as line separator.

    Does this happen also in your case?

    Imb.

    Tuesday, May 5, 2020 3:52 PM
  • I thought about doing just that.  However, the csv's are comma-delimited and there are comma's in some data.  With the Excel process in the VS app, a value like "AAA,BBB" in a field gets written to the csv as "AAA,BBB" in a column, which is the desired result.  If I try to import the csv into Access, it separates AAA and BBB into two columns, which is not the desired result.  I don't know how to get around that.

    I've had a variation of the Chr13/Cnr10 issue.  The Excel process in the VS app surrounds each field with quotes.  That's how the csv knows that all data between the quotes are together.  So data like "CC <carriage return> DD" gets written as "CC DD".  With the StreamWriter process, I checked for occurrence of Environment.NewLine and replace that with a space.    


    • Edited by em_rdh Tuesday, May 5, 2020 4:14 PM
    Tuesday, May 5, 2020 4:14 PM
  • If I try to import the csv into Access, it separates AAA and BBB into two columns, which is not the desired result.  I don't know how to get around that.

    Hi em_rdh,

    This is one of the many reasons why I use my own input routine, for far better control of what can happen.

    In the case that text-field contain comma's I use the following logic:

    - read the file line by line
    - split the line in an array using a comma as separator
    - loop through the elements of the array and write the content in the appropriate field, but …

    - when an element starts with a doublequote and has no endqoute, concatenate the element with the next element using a comma inbetween
    - when the next element has an ending doublequote the concatenated string is written in the field, else take again the next element, concatenate, etc.

    You should be able to do that too.

    Imb.

    • Marked as answer by em_rdh Tuesday, May 5, 2020 5:31 PM
    Tuesday, May 5, 2020 5:01 PM
  • I'll give that a try.  Thanks for the suggestion.
    Tuesday, May 5, 2020 5:30 PM
  • I'll give that a try.  Thanks for the suggestion.

    Hi em_rdh,

    Success with the job.

    If you need further assistence, don't hesitate to ask.

    Imb.

    Tuesday, May 5, 2020 7:21 PM