Solved

Exporting .csv to SQL DB

Posted on 2013-06-24
6
580 Views
Last Modified: 2013-06-25
I have to import .csv file to my sql database. CSV file is not in a proper format.

So. i manually change the format to an Excel spreadsheet and then import to sql database.

Now, i am working on a tool which will import csv file to database and do our necessary needs.

I have attached the sample format of csv and converted format of excel ( which i am doing it manually ). I request experts and throw their suggestion for importing a csv file to sql database.
Test-6619289--5-30-2013.csv
Test-6619289--5-30-2013.xls
0
Comment
Question by:chokka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 2

Accepted Solution

by:
Agneau earned 500 total points
ID: 39272264
Hello chokka,

I'm assuming that each file contain one invoice and each invoice can have several ships

My example attached implements a VBA code that parses your csv file into two user defined types (InvoiceHeader and InvoiceDetail).

There is also a procedure (InsertRecord) that can be modified just to insert the records already parsed into your SQL table. I'm also assuming that you know how to connect a SQL database and properly insert records into a table, otherwise let me know so I can provide you with a sample code to do it.



Option Explicit

Type InvoiceHeader
    Invoice_Date As Date
    Invoice_Number As Long
    Account_Number As Long
    DEA_Number As String
    Sales_Rep As Long
End Type

Type InvoiceDetail
    Ship_Qty As Long
    Order_Qty As Long
    Code As Variant
    UM As String
    Blank1 As String
    Description As String
    Blank2 As String
    Blank3 As String
    Blank4 As String
    CIN As Long
    PO_Number As String
    Due_Date As Date
    DEA_Class As Long
    Unit_Price As Double
    Extension As Double
End Type

Dim objFSO As Scripting.FileSystemObject
Dim FileTextStream As Scripting.TextStream

Sub MyCaller()
    Dim objDialog As Object
    Dim intResult As Integer
    
    Set objDialog = CreateObject("UserAccounts.CommonDialog")
    objDialog.Filter = "Text Files|*.csv|All Files|*.*"
    objDialog.FilterIndex = 1
    objDialog.InitialDir = "C:\"
    intResult = objDialog.ShowOpen

    If intResult = 0 Then
        Exit Sub
    End If
    
    Call ImportCSV(objDialog.FileName)
End Sub

Sub ImportCSV(FileName As String)
    Dim strInvoiceDetail As String
    Dim CurrentInvoiceHeader As InvoiceHeader
    Dim CurrentInvoiceDetail As InvoiceDetail
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    If objFSO.FileExists(FileName) Then
        Set FileTextStream = objFSO.OpenTextFile(FileName, ForReading)
        If Not FileTextStream.AtEndOfStream Then
            CurrentInvoiceHeader = GetInvoiceHeader()
            While FileTextStream.ReadLine <> "Ship Qty,Order Qty,Code,UM,,Description,,,,CIN,PO Number,Due Date,DEA Class,Unit Price,Extension"
            Wend
            strInvoiceDetail = FileTextStream.ReadLine
            While Left(strInvoiceDetail, 13) <> "Report Total:"
                CurrentInvoiceDetail = GetInvoiceDetail(strInvoiceDetail)
                Call InsertRecord(CurrentInvoiceHeader, CurrentInvoiceDetail)
                strInvoiceDetail = FileTextStream.ReadLine
            Wend
        End If
    Else
        MsgBox "File " & FileName & " does not exist", vbOKOnly + vbCritical, "Error"
    End If
    
End Sub

Private Function GetInvoiceHeader() As InvoiceHeader
    Dim HeaderFields() As String
    
    While FileTextStream.ReadLine <> "Invoice Date,Invoice Number,Account Number,DEA Number,Sales Rep,,,,,,,,,,"
    Wend

    HeaderFields = Split(FileTextStream.ReadLine, ",")
    
    GetInvoiceHeader.Invoice_Date = CDate(HeaderFields(0))
    GetInvoiceHeader.Invoice_Number = CLng(HeaderFields(1))
    GetInvoiceHeader.Account_Number = CLng(HeaderFields(2))
    GetInvoiceHeader.DEA_Number = HeaderFields(3)
    GetInvoiceHeader.Sales_Rep = CLng(HeaderFields(4))

End Function

Private Function GetInvoiceDetail(strRecord As String) As InvoiceDetail
    Dim DetailFields() As String
    
    DetailFields = Split(strRecord, ",")
    
    GetInvoiceDetail.Ship_Qty = CLng(DetailFields(0))
    GetInvoiceDetail.Order_Qty = CLng(DetailFields(1))
    GetInvoiceDetail.Code = DetailFields(2)
    GetInvoiceDetail.UM = DetailFields(3)
    GetInvoiceDetail.Blank1 = DetailFields(4)
    GetInvoiceDetail.Description = DetailFields(5)
    GetInvoiceDetail.Blank2 = DetailFields(6)
    GetInvoiceDetail.Blank3 = DetailFields(7)
    GetInvoiceDetail.Blank4 = DetailFields(8)
    GetInvoiceDetail.CIN = CLng(DetailFields(9))
    GetInvoiceDetail.PO_Number = DetailFields(10)
    GetInvoiceDetail.Due_Date = CDate(DetailFields(11))
    GetInvoiceDetail.DEA_Class = CLng(DetailFields(12))
    GetInvoiceDetail.Unit_Price = CDbl(DetailFields(13))
    GetInvoiceDetail.Extension = CDbl(DetailFields(14))

End Function

Private Sub InsertRecord(invHeader As InvoiceHeader, invDetail As InvoiceDetail)
    Dim lngRow As Long
        
    lngRow = Sheet1.Cells(1, 1).CurrentRegion.Rows.Count + 1
        
    Sheet1.Cells(lngRow, 1) = invDetail.Ship_Qty
    Sheet1.Cells(lngRow, 2) = invDetail.Order_Qty
    Sheet1.Cells(lngRow, 3) = invDetail.Code
    Sheet1.Cells(lngRow, 4) = invDetail.UM
    Sheet1.Cells(lngRow, 5) = invDetail.Blank1
    Sheet1.Cells(lngRow, 6) = invDetail.Description
    Sheet1.Cells(lngRow, 7) = invDetail.Blank2
    Sheet1.Cells(lngRow, 8) = invDetail.Blank3
    Sheet1.Cells(lngRow, 9) = invDetail.Blank4
    Sheet1.Cells(lngRow, 10) = invDetail.CIN
    Sheet1.Cells(lngRow, 11) = invDetail.PO_Number
    Sheet1.Cells(lngRow, 12) = invDetail.Due_Date
    Sheet1.Cells(lngRow, 13) = invDetail.DEA_Class
    Sheet1.Cells(lngRow, 14) = invDetail.Unit_Price
    Sheet1.Cells(lngRow, 15) = invDetail.Extension
    Sheet1.Cells(lngRow, 16) = invHeader.Invoice_Date
    Sheet1.Cells(lngRow, 17) = invHeader.Invoice_Number
    Sheet1.Cells(lngRow, 18) = invHeader.Account_Number
    Sheet1.Cells(lngRow, 19) = invHeader.DEA_Number
    Sheet1.Cells(lngRow, 20) = invHeader.Sales_Rep
        
End Sub

Open in new window

ImportCSV.xls
0
 

Author Comment

by:chokka
ID: 39272431
Thank you Agneau for your help !! Let me test the code and respond back to you.
0
 

Author Comment

by:chokka
ID: 39272521
Agneau, can you please give me the code in VB.Net or C#.Net ?

Although, i copied the code and fixed the syntax to the best i can in VB.Net Environment.

I couldn't find matching dll's for   Scripting.FileSystemObject

 Dim objFSO As Scripting.FileSystemObject
    Dim FileTextStream As Scripting.TextStream
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:chokka
ID: 39272605
Agneau, it works in the Excel.

Great , Thank you so much.

I will find a way to work it
0
 

Author Closing Comment

by:chokka
ID: 39272614
Thank you !!
0
 

Author Comment

by:chokka
ID: 39276432
@Agneau, Thank you for your help.

I have a small issue with Split(strRecord, ",")

You helped to split the record by comma and to insert into an excel workbook.

For Currency such as $1,000.10  - SplitRecord function for comma is throwing an exception.

How should i handle this issue ??


Private Function GetInvoiceDetail(strRecord As String) As InvoiceDetail
    Dim DetailFields() As String
    
    DetailFields = Split(strRecord, ",")
    
    GetInvoiceDetail.Ship_Qty = CLng(DetailFields(0))
    GetInvoiceDetail.Order_Qty = CLng(DetailFields(1))
    GetInvoiceDetail.Code = DetailFields(2)
    GetInvoiceDetail.UM = DetailFields(3)
    GetInvoiceDetail.Blank1 = DetailFields(4)
    GetInvoiceDetail.Description = DetailFields(5)
    GetInvoiceDetail.Blank2 = DetailFields(6)
    GetInvoiceDetail.Blank3 = DetailFields(7)
    GetInvoiceDetail.Blank4 = DetailFields(8)
    GetInvoiceDetail.CIN = CLng(DetailFields(9))
    GetInvoiceDetail.PO_Number = DetailFields(10)
    GetInvoiceDetail.Due_Date = CDate(DetailFields(11))
    GetInvoiceDetail.DEA_Class = CLng(DetailFields(12))
    GetInvoiceDetail.Unit_Price = CDbl(DetailFields(13))
    GetInvoiceDetail.Extension = CDbl(DetailFields(14))

End Function

Open in new window

Test-6619289--5-30-2013.csv
ImportCSV.xls
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
if statement with a sum of range cells 10 34
T-SQL: need to reset a declared variable 4 33
Excel Add-in Subscript out of range 5 31
VB .net 2010 Byte array 2 24
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

751 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