Solved

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

Posted on 2006-11-12
11
197 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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
 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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