Extract data from csv file
in [Programming]
|
Prev: Excel - Macro to rearrange/add columns in all the sheets basedon column header
Next: HELP!! Formula for calculating overtime
From: L.Mathe on 23 Feb 2010 10:05 Hi Joel, This code is BRILLIANT! I just need one other piece to it, I need also to extract the data in column 47 (note, the data is a 19 digit number so it must be defined as 'Text'. I tried to modify what you sent me as follows, but it didn't work: Do Data1 = CSVSht.Cells(c.Row, 110) Data2 = CSVSht.Cells(c.Row, 47) With ThisWorkbook.Sheets(DestSht) ..Range("A" & RowCount) = FName ..Range("B" & RowCount) = RowCount ..Range("C" & RowCount) = Data1 ..Range("D" & RowCount) = Data2 RowCount = RowCount + 1 End With I hope you can provide a little further assistance with this. The amount of manual work this piece of code will save is invaluable. Thank you -- Linda "joel" wrote: > > this code will open a folderPicker to get the correct folder and then > search every CSV file in the folder using column 77 and getting data in > column 110. > > > Sub GetData() > DestSht = "sheet1" > With ThisWorkbook.Sheets(DestSht) > SearchData = .Range("A1").Text > .Columns("A:B").NumberFormat = "@" > End With > > 'Declare a variable as a FileDialog object. > Dim fd As FileDialog > > 'Create a FileDialog object as a Folder Picker dialog box. > Set fd = Application.FileDialog(msoFileDialogFolderPicker) > > 'Declare a variable to contain the path > 'of each selected item. Even though the path is a String, > 'the variable must be a Variant because For Each...Next > 'routines only work with Variants and Objects. > Dim vrtSelectedItem As Variant > > 'Use a With...End With block to reference the FileDialog object. > With fd > > 'Use the Show method to display the File Picker dialog box and > return the user's action. > 'The user pressed the action button. > If .Show = -1 Then > > 'Step through each string in the FileDialogSelectedItems > collection. > For Each Folder In .SelectedItems > Call ReadCSV(Folder, SearchData, DestSht) > > Next Folder > End If > End With > > 'Set the object variable to Nothing. > Set fd = Nothing > End Sub > > Sub ReadCSV(ByVal Folder As Variant, _ > ByVal SearchData As String, _ > ByVal DestSht) > > Dim Data As String > > LastRow = ThisWorkbook.Sheets(DestSht) _ > .Range("A" & Rows.Count).End(xlUp).Row > NewRow = LastRow + 1 > RowCount = NewRow > FName = Dir(Folder & "\*.csv") > Do While FName <> "" > > Workbooks.OpenText Filename:=Folder & "\" & FName, _ > DataType:=xlDelimited, Comma:=True > Set CSVFile = ActiveWorkbook > Set CSVSht = CSVFile.Sheets(1) > 'check if data exists in column 77 > Set c = CSVSht.Columns(77).Find(what:=SearchData, _ > LookIn:=xlValues, lookat:=xlWhole) > If Not c Is Nothing Then > FirstAddr = c.Address > Do > Data = CSVSht.Cells(c.Row, 110) > With ThisWorkbook.Sheets(DestSht) > .Range("A" & RowCount) = FName > .Range("B" & RowCount) = RowCount > .Range("C" & RowCount) = Data > RowCount = RowCount + 1 > End With > Set c = CSVSht.Columns(77).FindNext(after:=c) > Loop While Not c Is Nothing And c.Address <> FirstAddr > End If > CSVFile.Close savechanges:=False > > FName = Dir() > Loop > End Sub > > > -- > joel > ------------------------------------------------------------------------ > joel's Profile: 229 > View this thread: http://www.thecodecage.com/forumz/showthread.php?t=180054 > > [url="http://www.thecodecage.com"]Microsoft Office Help[/url] > > . >
From: joel on 23 Feb 2010 10:21 I forgot that I had 1 minor error in the oringal posting that I didn't fix so it gave you the row number in the CSV file instead of the row number in the workbook. from Range("B" & RowCount) = RowCount to Range("B" & RowCount) = C.Row There werre two thing I did that weren't obvious to keep the data as text 1) Format columns A & b as Text from: .Columns("A:B").NumberFormat = "@" to: .Columns("A:D").NumberFormat = "@" 2) Use a variable that was declared as a string From: Dim Data As String To: Dim Data As String Dim Data1 As String Dim Data2 As String -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=180054 [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
From: joel on 22 Mar 2010 06:14
I will look at thsi tonight. To run the code you need to use VBA (Visual Basic) programming language that is part of Excel. If you open Excel and right click the tab (normally says sheet1) at the bottom of the worksheet and select "View Code". the from the VBA menu add a module using the menu Insert - Mode. then you cna paste the code from the posting into the module and run the code. the code won't run properly because the columns in your CSV file is different from the code you posted. The changes are minor. The Code opens CSV files and puts them into an Excel XLS workbook. So yo uwould save the results asa a workbook even thought your files were originally CSV files. I could save tthe results either as a CSV file or leavve them in a XLS workbook. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=180054 http://www.thecodecage.com/forumz/chat.php |