We help IT Professionals succeed at work.

CSV Stream writing in vb.net

tjpal
tjpal asked
on
790 Views
Last Modified: 2012-05-11
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.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
The error message is pretty self explanatory. Try with different data to pinpoint the source of error.

Author

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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
If you would scroll a bit down to the "  ODBC Microsoft Text Driver" section, you will get a 100% discount!
Ron MalmsteadInformation Services Manager
CERTIFIED EXPERT

Commented:
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.


Ron MalmsteadInformation Services Manager
CERTIFIED EXPERT

Commented:
..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

Author

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.
Ron MalmsteadInformation Services Manager
CERTIFIED EXPERT

Commented:
""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)

Author

Commented:
I did -  My.Computer.FileSystem.WriteAllText("C:\Test.csv", csvline, True)
gives me a good ol access denied

Author

Commented:
It wanted to create the file in root to where I want it created in my project folder wwwroot/theproject/csv
Ron MalmsteadInformation Services Manager
CERTIFIED EXPERT

Commented:
ahh...
Yes...

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

Dim rootPath As String = Server.MapPath("~")
My.Computer.FileSystem.WriteAllText(rootPath & "\Test.csv", csvline, True)
Ron MalmsteadInformation Services Manager
CERTIFIED EXPERT

Commented:
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.

Author

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,
Information Services Manager
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Mohamed AbowardaSenior Software Engineer
CERTIFIED EXPERT

Commented:

Author

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...
Ron MalmsteadInformation Services Manager
CERTIFIED EXPERT

Commented:
"Now, the only thing I need to do now, is prevent returns...""


...what do you mean by that ?

Author

Commented:
most of what I needed was there
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.