Import Muliple Text Files In One Folder Location Into One Long CSV

I have 500 or so customer files that are stored in individual text files for each customer.  Each customer file contains their orders.  I wanted to know if there is a way to import these files (all in one folder) into one big a CSV file for loading into a database.


Each text file in the folder takes the following format.  
     -The first line of the file is what is associated should be considered the "Customer Number".  It is an integer value followed by a colon, ":", and can take the form of a small number like 1 or 2 or a larger number like 503.
     -All other lines in the file should be considered order records for the customer indicated at the top of the file.   The first item, e.g. 1234 on the second line of the example below, should be considered "Item Number", the second item = "Quantity", and the third item = "date".  There are only three elements on each line following the first line (e.g. 1: does not have the normal three items).
 
1:
1234,3,2001-01-20
1234567,3,2001-01-23
123456,6,2004-12-21
123,2,2004-11-23
741852,4,2005-05-28
987654321,2,2006-09-23

In the CSV output I would like to place all the orders with the customer number inserted as the first item in the CSV.  Right now, each customer has an individual text file where all their orders are housed.  So basically the CSV is going to be one long file that combines all the .txt files in folder X and takes the customer number an transposes it.

#CustomerNumber,ItemNumber,Quanitity,Date
1,1234,3,2001-01-20
1,1234567,3,2001-01-23
1,123456,6,2004-12-21
1,123,2,2004-11-23
1,741852,4,2005-05-28
1,987654321,2,2006-09-23
2,12345,2,2006-09-24

Any help would be appreciated, as doing these by hand would take a very long time.
endrecAsked:
Who is Participating?
 
Fernando SotoRetiredCommented:
Hi endrec;

This should do what you need. If you have any questions let me know.

    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click

        ' The 1st param is the customer directory and the 2nd is the name
        ' and path of where to write the output file
        CombinCustomerFiles("C:\Temp\CustomerData\folderwithtxtfiles", "C:\Temp\Data.out")

    End Sub

    Private Sub CombinCustomerFiles(ByVal dir As String, ByVal outputFile As String)

        If Not Directory.Exists(dir) Then
            MessageBox.Show("The directory " & dir & " does not exist")
            Return
        End If

        If File.Exists(outputFile) Then
            If MessageBox.Show("Output file " & outputFile & " exist. Do you wish to overwrite (Yes/No)", _
                "Overwrite Output File", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, _
                MessageBoxDefaultButton.Button2) <> DialogResult.Yes Then
                Return
            End If
        End If

        Dim oFile As New StreamWriter(outputFile, False)
        Dim dInfo As New DirectoryInfo(dir)
        Dim fileList() As FileInfo = dInfo.GetFiles("*.txt")
        For Each inFile As FileInfo In fileList
            Dim sr As New StreamReader(inFile.FullName)
            Dim line As String = String.Empty
            Dim custNum As String = String.Empty
            While sr.Peek() <> -1
                line = sr.ReadLine().Trim
                If line <> String.Empty Then
                    If line.IndexOf(":"c) >= 0 Then
                        custNum = line.Substring(0, line.IndexOf(":"c))
                    Else
                        oFile.WriteLine(custNum & "," & line)
                    End If
                End If
            End While
        Next
        oFile.Close()

    End Sub


Fernando
0
 
endrecAuthor Commented:
0
 
HillwaaaCommented:
Hi endrec,

Here's the basis for what you'll need - note that I haven't put in any error handling, but you get the idea.  The other thing you'll need to add is either a list of all the files and a loop to go through them, or functionality to open all the files in a directory.  

        Dim fs As New FileStream("targetFile.csv", FileMode.Create, FileAccess.Write)
        Dim outStream As New StreamWriter(fs)

        Dim fileLocation As String = "c:\temp\file.txt" 'CHANGE THIS

        Dim inStream As StreamReader
        inStream = New StreamReader(fileLocation)

        Dim custNumber As Integer
        custNumber = CInt(inStream.ReadLine())

        Dim tempString As String


        While inStream.Peek() > -1
            'peek method of StreamReader object tells how much more data is left in the file
            tempString = inStream.ReadLine()
            outStream.WriteLine(custNumber & tempString)
        End While




Cheers!
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
endrecAuthor Commented:
I received an error that is preventing the I/O code from working.  To the top of the form class I added "Imports System.IO".  Is there something I need to do to get the code to run (specifically Fernando's)?

System.UnauthorizedAccessException was unhandled
  Message="Access to the path 'C:\Customers\training_set' is denied."
  Source="mscorlib"
  StackTrace:
       at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
       at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
       at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)
       at System.IO.StreamWriter.CreateFile(String path, Boolean append)
       at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
       at System.IO.StreamWriter..ctor(String path, Boolean append)
       at CustomerDataSetConverter.frmMain.CombinCustomerFiles(String dir, String outputFile) in C:\Documents and Settings\Owner\My Documents\Visual Studio 2005\Projects\CustomerDataSetConverter\CustomerDataSetConverter\frmMain.vb:line 26
       at CustomerDataSetConverter.frmMain.btnExecute_Click(Object sender, EventArgs e) in C:\Documents and Settings\Owner\My Documents\Visual Studio 2005\Projects\CustomerDataSetConverter\CustomerDataSetConverter\frmMain.vb:line 8
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at CustomerDataSetConverter.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
0
 
HillwaaaCommented:
You do not have permission to create the file "C:\Customers\training_set".

Either change this to a directory that you have permissions for (like most likeley "C:\temp\training_set"), or grant yourself permissions to the above directory.

Cheers,
Hillwaaa
0
 
endrecAuthor Commented:
I am the admin account on the computer.  I went ahead and checked that I have full control over the folder and that the permission extended to the files in the folder.  I also checked to make sure that the folder and files are not read only.
0
 
Fernando SotoRetiredCommented:
It looks like a security issue. Seeming you are an administrator also check to make sure that the program has privileges to run. You can do that in Control Panel -> Administrator Tools -> Microsoft .Net Freamwork Confinguration.
0
 
endrecAuthor Commented:
Got the code to work.  The problem was that I was referecing two directories in CombinCustomerFiles("C:\Temp\CustomerData\folderwithtxtfiles", "C:\Temp\Data.out") instead of one directory and one file path.
0
 
Fernando SotoRetiredCommented:
Glad to see that it is working. Have a great day. ;=)
0
 
FordraidersCommented:
Fernando, hope you are well..
Your solution...can it be applied to vba code ?

Thanks
fordraiders
0
 
Fernando SotoRetiredCommented:
@fordraiders, sorry but I am not well versed in VBA to be able to answer your question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.