Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2006-11-12
Medium Priority
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 600 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 64

Accepted Solution

Fernando Soto earned 1400 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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.


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 64

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 64

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 64

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

886 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