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

Posted on 2006-11-12
Last Modified: 2016-02-14
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).

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.


Any help would be appreciated, as doing these by hand would take a very long time.
Question by:endrec
  • 4
  • 4
  • 2
  • +1

Author Comment

ID: 17927458
LVL 16

Assisted Solution

Hillwaaa earned 150 total points
ID: 17927608
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

LVL 62

Accepted Solution

Fernando Soto earned 350 total points
ID: 17927687
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")
        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
            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))
                        oFile.WriteLine(custNum & "," & line)
                    End If
                End If
            End While

    End Sub


Author Comment

ID: 17928131
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."
       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()
LVL 16

Expert Comment

ID: 17928192
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.

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.


Author Comment

ID: 17928226
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.
LVL 62

Expert Comment

by:Fernando Soto
ID: 17929552
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.

Author Comment

ID: 17936123
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.
LVL 62

Expert Comment

by:Fernando Soto
ID: 17938189
Glad to see that it is working. Have a great day. ;=)

Expert Comment

ID: 40677324
Fernando, hope you are well..
Your solution...can it be applied to vba code ?

LVL 62

Expert Comment

by:Fernando Soto
ID: 40680010
@fordraiders, sorry but I am not well versed in VBA to be able to answer your question.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now