Solved

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

Posted on 2006-11-12
11
190 Views
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).
 
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.
0
Comment
Question by:endrec
  • 4
  • 4
  • 2
  • +1
11 Comments
 

Author Comment

by:endrec
ID: 17927458
0
 
LVL 16

Assisted Solution

by:Hillwaaa
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




Cheers!
0
 
LVL 62

Accepted Solution

by:
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")
            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
 

Author Comment

by:endrec
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."
  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
 
LVL 16

Expert Comment

by:Hillwaaa
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.

Cheers,
Hillwaaa
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:endrec
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.
0
 
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.
0
 

Author Comment

by:endrec
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.
0
 
LVL 62

Expert Comment

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

Expert Comment

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

Thanks
fordraiders
0
 
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

746 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

7 Experts available now in Live!

Get 1:1 Help Now