• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 767
  • Last Modified:

CSV Stream writing in vb.net

Hi,

I am writing a csv file and the csv halts on one of my text fields.  Is there a 255 character limit?

The code I have used is :  

 Dim objConn As System.Data.OleDb.OleDbConnection
            Dim objCmd As System.Data.OleDb.OleDbCommand
            Dim strConnString, strSQL As String
            Dim i As Integer
            Dim strPath As String = "csvfolder/"
            Dim strFileName As String = "csvExport.csv"
            '*** Create Text Files (Columns Default ***' ***'
            Dim StrWer As StreamWriter
            StrWer = File.CreateText(Server.MapPath(strPath) & strFileName)
            StrWer.Write("SR_ID, SR_Name, SR_Name_Show, SR_Address, SR_City, SR_State, SR_zip, SR_Phone, SR_Phone_Show, SR_Fax, SR_Fax_Show, SR_Email, SR_JobID, SR_OpeningsID, SR_JobTitle, JobTypeCode, SR_MCA_Other_Text, SR_SalaryRangeBase, SR_SalaryRangeHigh, SR_LocName, SR_City1, SR_State1, SR_Zip1, SR_PosReqs, PositionSummary, SR_Date, SR_Discipline_ID, SR_Job_Status") '" & _
            '"  ', SR_MCA_Other_Text, SR_SalaryRangeBase, SR_SalaryRangeHigh, SR_LocName, SR_City1, " & _
            '" SR_State1, SR_Zip1, SR_PosReqs, PositionSummary, SR_Date, userid, SR_Discipline_ID, SR_Job_Status, postedBy, cashBonus, closedToWomen")
            StrWer.Close()


            '*** Connect to CSV ***'
            strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(strPath) & _
            ";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"
            objConn = New System.Data.OleDb.OleDbConnection(strConnString)
            objConn.Open()


            Dim SR_ID, SR_Name, SR_Name_Show, SR_Address, SR_City, SR_State, SR_zip, SR_Phone, SR_Phone_Show, SR_Fax, SR_Fax_Show, SR_Email, SR_JobID, SR_OpeningsID, SR_JobTitle, JobTypeCode, SR_MCA_Other_Text, SR_SalaryRangeBase, SR_SalaryRangeHigh, SR_LocName, SR_City1, SR_State1, SR_Zip1, SR_PosReqs, PositionSummary, SR_Date, SR_Discipline_ID, SR_Job_Status As Label

            For i = 0 To myRepeater.Items.Count - 1
                SR_ID = myRepeater.Items(i).FindControl("SR_ID")
                SR_Name = myRepeater.Items(i).FindControl("SR_Name")
                SR_Name_Show = myRepeater.Items(i).FindControl("SR_Name_Show")
                SR_Address = myRepeater.Items(i).FindControl("SR_Address")
                SR_City = myRepeater.Items(i).FindControl("SR_City")
                SR_State = myRepeater.Items(i).FindControl("SR_State")
                SR_zip = myRepeater.Items(i).FindControl("SR_zip")
                SR_Phone = myRepeater.Items(i).FindControl("SR_Phone")
                SR_Phone_Show = myRepeater.Items(i).FindControl("SR_Phone_Show")
                SR_Fax = myRepeater.Items(i).FindControl("SR_Fax")
                SR_Fax_Show = myRepeater.Items(i).FindControl("SR_Fax_Show")
                SR_Email = myRepeater.Items(i).FindControl("SR_Email")
                SR_JobID = myRepeater.Items(i).FindControl("SR_JobID")
                SR_OpeningsID = myRepeater.Items(i).FindControl("SR_OpeningsID")
                SR_JobTitle = myRepeater.Items(i).FindControl("SR_JobTitle")
                JobTypeCode = myRepeater.Items(i).FindControl("JobTypeCode")
                SR_MCA_Other_Text = myRepeater.Items(i).FindControl("SR_MCA_Other_Text")
                SR_SalaryRangeBase = myRepeater.Items(i).FindControl("SR_SalaryRangeBase")
                SR_SalaryRangeHigh = myRepeater.Items(i).FindControl("SR_SalaryRangeHigh")
                SR_LocName = myRepeater.Items(i).FindControl("SR_LocName")
                SR_City1 = myRepeater.Items(i).FindControl("SR_City1")
                SR_State1 = myRepeater.Items(i).FindControl("SR_State1")
                SR_Zip1 = myRepeater.Items(i).FindControl("SR_Zip1")
                SR_PosReqs = myRepeater.Items(i).FindControl("SR_PosReqs")
                PositionSummary = myRepeater.Items(i).FindControl("PositionSummary")
                SR_Date = myRepeater.Items(i).FindControl("SR_Date")
                SR_Discipline_ID = myRepeater.Items(i).FindControl("SR_Discipline_ID")
                SR_Job_Status = myRepeater.Items(i).FindControl("SR_Job_Status")
                ', SR_SalaryRangeBase, SR_SalaryRangeHigh, SR_LocName, SR_City1, SR_State1, SR_Zip1, SR_PosReqs, PositionSummary, SR_Date, SR_Discipline_ID, SR_Job_Status) " & _
                strSQL = "INSERT INTO " & strFileName & " (SR_ID, SR_Name, SR_Name_Show, SR_Address, SR_City, SR_State, SR_zip, SR_Phone, SR_Phone_Show, SR_Fax, SR_Fax_Show, SR_Email, SR_JobID, SR_OpeningsID, SR_JobTitle, JobTypeCode, SR_MCA_Other_Text) " & _
                "VALUES (" & _
                " '" & SR_ID.Text & "', " & _
                " '" & SR_Name.Text & "', " & _
                " '" & SR_Name_Show.Text & "', " & _
                " '" & SR_Address.Text & "', " & _
                " '" & SR_City.Text & "', " & _
                " '" & SR_State.Text & "', " & _
                " '" & SR_zip.Text & "', " & _
                " '" & SR_Phone.Text & "', " & _
                " '" & SR_Phone_Show.Text & "', " & _
                " '" & SR_Fax.Text & "', " & _
                " '" & SR_Fax_Show.Text & "', " & _
                " '" & SR_Email.Text & "', " & _
                " '" & SR_JobID.Text & "', " & _
                " '" & SR_OpeningsID.Text & "', " & _
                " '" & SR_JobTitle.Text & "', " & _
                " '" & JobTypeCode.Text & "', " & _
                " '" & SR_MCA_Other_Text.Text & "')"
                '" '" & SR_SalaryRangeBase.Text & "', " & _
                '" '" & SR_SalaryRangeHigh.Text & "', " & _
                '" '" & SR_LocName.Text & "', " & _
                '" '" & SR_City1.Text & "', " & _
                '" '" & SR_State1.Text & "', " & _
                '" '" & SR_Zip1.Text & "', " & _
                '" '" & SR_PosReqs.Text & "', " & _
                '" '" & PositionSummary.Text & "', " & _
                '" '" & SR_Date.Text & "', " & _
                '" '" & SR_Discipline_ID.Text & "', " & _
                '" '" & SR_Job_Status.Text & "')"


                objCmd = New System.Data.OleDb.OleDbCommand()
                With objCmd
                    .Connection = objConn
                    .CommandType = CommandType.Text
                    .CommandText = strSQL
                End With

                objCmd.ExecuteNonQuery()

            Next

            objCmd = Nothing
            objConn.Close()
            objConn = Nothing

        End If



My Error message- The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data  


Am I writing the file incorrectly?  I didn't know there would be a cell limit.

Thanks
T.J.
0
tjpal
Asked:
tjpal
  • 8
  • 7
  • 3
  • +1
1 Solution
 
CodeCruiserCommented:
The error message is pretty self explanatory. Try with different data to pinpoint the source of error.
0
 
tjpalAuthor Commented:
yes, I know where its at - but when the csv is written the text field seems to be where it stops - Is there a 255 chatracter limit to a csv cell?
0
 
CodeCruiserCommented:
I dont think the limit is on CSV itself as its a plain text file. The limit is in JET if it is.

Try using Microsoft Text Driver

http://www.csvreader.com/csv_benchmarks.php
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
tjpalAuthor Commented:
um yeah not paying for csvwriter..

All I really want to do is simply create a csv from my query provided and not have it choke when I have a cell with  a large number of text.

I didn't think this would be so much of a headache..  asp classic did it nicely
0
 
CodeCruiserCommented:
If you would scroll a bit down to the "  ODBC Microsoft Text Driver" section, you will get a 100% discount!
0
 
Ron MalmsteadInformation Services ManagerCommented:
Why not just build comma delim' strings and write them to the file ?
You shouldn't need to do an "INSERT" with OLEdb driver to write a csv file.


0
 
Ron MalmsteadInformation Services ManagerCommented:
..something like this maybe..

     
For i = 0 To myRepeater.Items.Count - 1
            SR_ID = myRepeater.Items(i).FindControl("SR_ID")
            SR_Name = myRepeater.Items(i).FindControl("SR_Name")
            SR_Name_Show = myRepeater.Items(i).FindControl("SR_Name_Show")
            SR_Address = myRepeater.Items(i).FindControl("SR_Address")
            SR_City = myRepeater.Items(i).FindControl("SR_City")
            SR_State = myRepeater.Items(i).FindControl("SR_State")
            SR_zip = myRepeater.Items(i).FindControl("SR_zip")
            SR_Phone = myRepeater.Items(i).FindControl("SR_Phone")
            SR_Phone_Show = myRepeater.Items(i).FindControl("SR_Phone_Show")
            SR_Fax = myRepeater.Items(i).FindControl("SR_Fax")
            SR_Fax_Show = myRepeater.Items(i).FindControl("SR_Fax_Show")
            SR_Email = myRepeater.Items(i).FindControl("SR_Email")
            SR_JobID = myRepeater.Items(i).FindControl("SR_JobID")
            SR_OpeningsID = myRepeater.Items(i).FindControl("SR_OpeningsID")
            SR_JobTitle = myRepeater.Items(i).FindControl("SR_JobTitle")
            JobTypeCode = myRepeater.Items(i).FindControl("JobTypeCode")
            SR_MCA_Other_Text = myRepeater.Items(i).FindControl("SR_MCA_Other_Text")
            SR_SalaryRangeBase = myRepeater.Items(i).FindControl("SR_SalaryRangeBase")
            SR_SalaryRangeHigh = myRepeater.Items(i).FindControl("SR_SalaryRangeHigh")
            SR_LocName = myRepeater.Items(i).FindControl("SR_LocName")
            SR_City1 = myRepeater.Items(i).FindControl("SR_City1")
            SR_State1 = myRepeater.Items(i).FindControl("SR_State1")
            SR_Zip1 = myRepeater.Items(i).FindControl("SR_Zip1")
            SR_PosReqs = myRepeater.Items(i).FindControl("SR_PosReqs")
            PositionSummary = myRepeater.Items(i).FindControl("PositionSummary")
            SR_Date = myRepeater.Items(i).FindControl("SR_Date")
            SR_Discipline_ID = myRepeater.Items(i).FindControl("SR_Discipline_ID")
            SR_Job_Status = myRepeater.Items(i).FindControl("SR_Job_Status")
            
            Dim csvline As String = ""
         csvline =      """" & SR_ID.Text & """" & "," & _
                        """" & SR_Name.Text & """" & "," & _
                       """" & SR_Name_Show.Text & """" & "," & _
                       """" &  SR_Address.Text & """" & "," & _
                       """" & SR_City.Text & """" & "," & _
                         """" & SR_State.Text & """" & "," & _
                        """" & SR_zip.Text & & """" & "," & _
                       """" &  SR_Phone.Text & """" & "," & _
                        """" & SR_Phone_Show.Text & """" & "," & _
                        """" &  SR_Fax.Text & """" & "," & _
                        """" & SR_Fax_Show.Text& """" & "," & _
                        """" & SR_Email.Text & """" & "," & _
                         """" & SR_JobID.Text & """" & "," & _
                         """" & SR_OpeningsID.Text & """" & "," & _
                         """" &  SR_JobTitle.Text & """" & "," & _
                         """" & JobTypeCode.Text & """" & "," & _
                         """" &  SR_MCA_Other_Text.Text & """" & "," & _
                         """" &  SR_SalaryRangeBase.Text & """" & "," & _
                         """" &  SR_SalaryRangeHigh.Text & """" & "," & _
                        """" &  SR_LocName.Text & """" & "," & _
                        """" &  SR_City1.Text & """" & "," & _
                        """" &  SR_State1.Text & """" & "," & _
                       """" &  SR_Zip1.Text & """" & "," & _
                         """" & SR_PosReqs.Text & """" & "," & _
                         """" & PositionSummary.Text & """" & "," & _
                         """" &  SR_Date.Text & """" & "," & _
                         """" &  SR_Discipline_ID.Text & """" & "," & _
                         """" & SR_Job_Status.Text & """"
            My.Computer.FileSystem.WriteAllText("C:\Test.csv", csvline, True)

        Next

Open in new window

0
 
tjpalAuthor Commented:
I tried one line and its a large amount of text 1000 characters or so.  adn I get :

The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data.
0
 
Ron MalmsteadInformation Services ManagerCommented:
""The field is too small to accept the amount of data you attempted to add. ""
That is the jet txt driver speaking...

1000 characters is not a lot.
Did you actually try my code ?
 
My.Computer.FileSystem.WriteAllText("C:\Test.csv", csvline, True)

0
 
tjpalAuthor Commented:
I did -  My.Computer.FileSystem.WriteAllText("C:\Test.csv", csvline, True)
gives me a good ol access denied
0
 
tjpalAuthor Commented:
It wanted to create the file in root to where I want it created in my project folder wwwroot/theproject/csv
0
 
Ron MalmsteadInformation Services ManagerCommented:
ahh...
Yes...

Change the path to match..
Try this...

Dim rootPath As String = Server.MapPath("~")
My.Computer.FileSystem.WriteAllText(rootPath & "\Test.csv", csvline, True)
0
 
Ron MalmsteadInformation Services ManagerCommented:
Sorry...

My.Computer.FileSystem.WriteAllText(rootPath & "\csv\Test.csv", csvline, True)


...I think that's right.  I'm not in a spot I can debug it though.
Just make sure the dir exists and you have permission to write to it.
0
 
tjpalAuthor Commented:
So I did some research, and I found what I was looking for except for one thing I found.  

the following code:

  Dim filePath As String = Server.MapPath("test.csv")
        Dim delimiter As String = ","

        Dim longtext As String
        longtext = "There are many variations of passages of Lorem Ipsum available but the majority have suffered alteration in some form by injected humour or randomised words which don't look even slightly believable. If you are going to use a passage of Lorem Ipsum you need to be sure there isn't anything embarrassing hidden in the middle of text. All the Lorem Ipsum generators on the Internet tend to repeat predefined chunks as necessary making this the first true generator on the Internet. It uses a dictionary of over 200 Latin words combined with a handful of model sentence structures to generate Lorem Ipsum which looks reasonable. The generated Lorem Ipsum is therefore always free from repetition injected humour or non-characteristic words etc."
        Dim output As String()() = New String()() _
        {New String() {"Col 1 Row 1", "Col 2 Row 1", "Col 3 Row 1"}, _
         New String() {"Col1 Row 2", "Col2 Row 2", (longtext)}}

        Dim length As Integer = output.GetLength(0)
        Dim sb As New StringBuilder()

        For index As Integer = 0 To length - 1
            sb.AppendLine(String.Join(delimiter, output(index)))
        Next

        File.WriteAllText(filePath, sb.ToString())



It works exactly what I wanted it to do, I will be writing out my column headers in row 1 and will output my text in row 2.

It generates the file nicely, except for when the long text I have right now has no commas in it, but of course when there is a comma it will go to a new cell.  

Any ideas?  If you copy the code and paste it and have Button1 on the page it will run.

If you throw a comma in the text, you'll see it go to the next cell.

The purpose to this is to generate a request and it has to be submited to a board that accepts the csv.

Thanks,
0
 
Ron MalmsteadInformation Services ManagerCommented:
Ahh...

You have to use 1 of 2 strategies here.
1 ..escape the commas.
2 ..use quoted identifiers.

This means...
Quoted identifier: "value1,value2","value3"
Escaped comma: value1~,value2,value3
...where ~ is the escape character I randomly chose to use.

Both of those examples only have two fields.  value1 and value 2 are in the same field.

My previous example used quoted identifiers.

Once you decide on a strategy you have to process it in code.

Example...this will take the below string "str"... change from single to double quote identifiers, then split it into lines, then split it into individual fields.

        Dim str As String = "'test1,test2','test3','test4'" & vbCrLf & "'test','test2,test3','test4'" & vbCrLf & "'test1','test2','test3,test4'"
        str = str.Replace("'", """")
        Dim strarray As String() = Str.split(vbCrLf)
        Dim csvline As String() = Nothing

        For Each line In strarray
            Console.WriteLine(line)
            Dim thisline As String() = line.Replace("""" & "," & """", "~").Replace("""", "").Split("~")
            For Each field In thisline
                Console.WriteLine(field)
            Next
        Next


0
 
Mohamed AbowardaSoftware EngineerCommented:
0
 
tjpalAuthor Commented:
I believe I got it.  This is what I have put together:

   Dim filePath As String = Server.MapPath("healthecareers/test.csv")
            Dim delimiter As String = ","

            Dim longtext As String
            longtext = "There are many, variations' of passages of Lorem Ipsum available but the majority have suffered alteration in some form by injected humour or randomised words which don't look even slightly believable. If you are going to use a passage of Lorem Ipsum you need to be sure there isn't anything embarrassing hidden in the middle of text. All the Lorem Ipsum generators on the Internet tend to repeat predefined chunks as necessary making this the first true generator on the Internet. It uses a dictionary of over 200 Latin words combined with a handful of model sentence structures to generate Lorem Ipsum which looks reasonable. The generated Lorem Ipsum is therefore always free from repetition injected humour or non-characteristic words etc."
            'Dim charsToTrim() As Char = {","c, "."c, " "c}
            'Replace(longtext, "'", "''", ",", ",")

            Dim output As String()() = New String()() _
            {New String() {"Col 1 Row 1", "Col 2 Row 1", "Col 3 Row 1"}, _
             New String() {"Col1 Row 2", "Col2 Row 2", (Replace(longtext, ",", ",",))}}

            Dim length As Integer = output.GetLength(0)
            Dim sb As New StringBuilder()
            'sb = sb.Replace(",", "~")

            For index As Integer = 0 To length - 1
                sb.AppendLine(String.Join(delimiter, output(index)))
            Next

            File.WriteAllText(filePath, sb.ToString())


            Response.Write(longtext)
            Response.End()


Now, the only thing I need to do now, is prevent returns...
0
 
Ron MalmsteadInformation Services ManagerCommented:
"Now, the only thing I need to do now, is prevent returns...""


...what do you mean by that ?
0
 
tjpalAuthor Commented:
most of what I needed was there
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now