[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert CSV to Excel file format

Posted on 2006-05-09
18
Medium Priority
?
431 Views
Last Modified: 2008-01-09
Title says it all.
0
Comment
Question by:CDCOP
  • 7
  • 6
  • 2
  • +1
17 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16645609
Add a Reference to MS Excel 10.0 Object Library
Then

Dim xlApp as New Excel.Application
xlApp.WorkBooks.Open (FileName:="YourCSVFile.Txt", Format:=Excel.FileFormat.xlCSV, Delimiter:=",")
xlApp.visible=true

0
 
LVL 9

Author Comment

by:CDCOP
ID: 16649071
It still needs to be converted and saved. I don't want any interaction with excel.
0
 
LVL 9

Author Comment

by:CDCOP
ID: 16649202
BTW. I couldn't get the above code to run with the reference.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 96

Expert Comment

by:Bob Learned
ID: 16650362
One way to do that is to convert to a very specifically-formatted XML document, which is what I believe Excel documents are heading towards, but the process is not simple.

Bob
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 16655364
Well the code I posted should work, if you post the error you are getting may be someone can help.
But then it seems you do not want to use the Excel Object.
Now as Bob says you can rather convert the TXT file to say an specifically-formatted XML file so that the Excel reads it directly.
But what I understand is that the file would be an XML file only, it cannnot have the native XLS extension is it ??

Now in that case, we can just convert the TXT file to a CSV File, i.e.
System.IO.File.Copy("Source.Txt","Destination.CSV")
Now this CSV would open just fine in excel.
The downside is you can not have any granular control over this CSV file like say formatting etc. while the XML might have it. But then it is just a single line of code to get the CSV.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16657224
Yeah, but Excel will still open it just like a XLS Workbook, because it has XML markup to control it's behavior, so what's the difference?

Bob
0
 
LVL 9

Author Comment

by:CDCOP
ID: 16659514
Bob,

I am using the following code (for the most part) to open an excel file and transfer the data into access. As you can see in the select statement, it is looking for an excel format when opening. Is there a way to change this to recognize a CSV format and still use the same code to xfer to the DB?

----------------------------------
    Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\Diary.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
    Dim sourceSQL As String = "SELECT * FROM [Sheet1$]"
    Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\Diary.mdb;User Id=admin;Password=;"
    Dim targetSQL As String = "SELECT * FROM DiaryRecord"


Private Sub TransferWithDataReader()

        Dim ticks As Long = Now.Ticks
        Dim counter As Long = 0

        'to save hard coding, I used dataadapter for target to ...
        '... create insert command, including parameter collection
        Dim targetCon As New OleDbConnection(targetConStr)
        Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon)
        Dim cb As New OleDbCommandBuilder(targetDA)
        Dim targetCommand As OleDbCommand = cb.GetInsertCommand

        'now do the work
        Dim sourceCon As New OleDbConnection(sourceConStr)
        Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
        targetCommand.Connection.Open()
        sourceCon.Open()
        Dim sourceReader As OleDbDataReader
        sourceReader = sourceCommand.ExecuteReader()
        While sourceReader.Read()
            'for each row from source
            For i As Integer = 0 To sourceReader.FieldCount - 1
                'load values into parameters
                targetCommand.Parameters(i).Value = sourceReader(i)
                'if any of the source data needs to be modified ...
                '... for the target, it could be done here ...
            Next
            '... or here
            'then write to target
            targetCommand.ExecuteNonQuery()
            'report progress
            counter += 1
            If counter Mod 100 = 0 Then
                Label1.Text = counter.ToString & " records transferred"
                Label1.Refresh()
            End If
        End While
        sourceReader.Close()
        sourceCon.Close()
        targetCommand.Connection.Close()

        Dim secondstaken As Long = (Now.Ticks - ticks) / TimeSpan.TicksPerSecond
        Label1.Text = secondstaken.ToString & " seconds to complete " & counter.ToString & " records"

    End Sub

-----------------------------
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16659690
The text driver uses the folder:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""

Bob
0
 
LVL 9

Author Comment

by:CDCOP
ID: 16659899
I get:

System.Data.OleDb.OleDbException was unhandled
  ErrorCode=-2147467259
  Message="The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data."
 

on line: sourceReader = sourceCommand.ExecuteReader()
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16659967
Are you specifying just the folder path in the connection string or the full-path file name?   With the Text driver, you use folder path, and the select statement should indicate the file name.

Bob
0
 
LVL 9

Author Comment

by:CDCOP
ID: 16660104
Yes, it finds the file. I figured out what it was doing as far as the file name and select statement. This error came after I corrected the filename/select statement.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16660129
1) What kind of application is this?  

2) What is the .NET version
0
 
LVL 9

Author Comment

by:CDCOP
ID: 16660318
VB, .NET 2.0, VS 2005
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 16660343
1) What kind of application?  ASP.NET, WinForms, Web Service, Windows Service, ...?

Bob
0
 
LVL 9

Author Comment

by:CDCOP
ID: 16660613
Winforms
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16661672
Can you please paste the actual code you are using in place of these two lines

    Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\Diary.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
    Dim sourceSQL As String = "SELECT * FROM [Sheet1$]"

Leave in, but change to ***, any userid or password info in the connection string.  I'm assuming that you have checked and believe that neither of the assertions in the error message - permissions problems or file in use - is true.

Roger
0
 
LVL 34

Accepted Solution

by:
Sancler earned 2000 total points
ID: 16663561
The code you give above is precisely that which I used in the test program reported in

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21819012.html

It transferred 8800 12-field records from Excel to Access in 21 seconds on my system with my data.  I said then that you would need to change the connection strings and sql statements to fit your data and that, if it was not a "straight" transfer - identical fields in an identical order from source to target - some coding revision might be necessary.

Luckily I still have the test program and data.  I have now run it again, having created a .csv file of the same data.  This is a copy and paste of the declaration code in the revised program.  The operating code is unchanged.

    'Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\Diary.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
    Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
    'Dim sourceSQL As String = "SELECT * FROM [Sheet1$]"
    Dim sourceSQL As String = "SELECT * FROM DiaryRecord.csv"
    Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\Diary.mdb;User Id=admin;Password=;"
    Dim targetSQL As String = "SELECT * FROM DiaryRecord"

I am getting similar results: 8800 12-field records transferred from the .csv file to Access in 21 secs.

The code was originally written and tested - for Excel to Access - in VB.NET 2003.  The above modification for, and test of, .csv to Access was also done in VB.NET 2003.  But I also transferred the modified code to a VB.NET 2005 project and tested in that.  That did the transfer in 24 seconds.  

I find it interesting that it should be ~14% slower in 2005 than in 2003, and I'll try and find time to discover why.  But that is a different issue.  The point for present purposes is that with the code that you quote and the modifications for .csv that have been suggested in this thread, it works for me on either 2003 or 2005.  So what is different about your code and/or your system and/or your data?

Roger
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question