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 17 Feb 2010 11:18 I was looking through this discussion group, found something close to what I need, but not being a programmer, I haven't been able to modify it to what I am attempting to do and I hope someone can help. The .csv files are split into groups by month (ie: "c:\Jan\file name.csv)". I need to search within the group of csv files and extract data into an Excel file. What I would like to do is if Cell A1 in my active wb matches the data to the right of the 76 comma in the csv file, extract the 'text' value (must be specified as text as this data is a 19 digit number and can't have it tuncated), in cell A2. Then in cell B2, extract the data that is to the right of the 109 comma. Continue searching the current file and loop through all remaining files, extract subsequent data into the next line below. Hopefully this is possible and someone can help! Thanks -- Linda
From: KC on 17 Feb 2010 20:05 Interesting exercise. I am guessing that you have one workbook with one worksheet where A1, B1 only are filled. Nothing further; Only search in each csv file for 76th and 109th comma, In what way is the matching done please? as the following 19 positions are DIGITS only. "L.Mathe" <LMathe(a)discussions.microsoft.com> wrote in message news:0A02796E-EF3A-4B89-952B-F5BDE3BC2BDC(a)microsoft.com... >I was looking through this discussion group, found something close to what >I > need, but not being a programmer, I haven't been able to modify it to what > I > am attempting to do and I hope someone can help. > > The .csv files are split into groups by month (ie: "c:\Jan\file > name.csv)". > I need to search within the group of csv files and extract data into an > Excel > file. What I would like to do is if Cell A1 in my active wb matches the > data > to the right of the 76 comma in the csv file, extract the 'text' value > (must > be specified as text as this data is a 19 digit number and can't have it > tuncated), in cell A2. Then in cell B2, extract the data that is to the > right of the 109 comma. Continue searching the current file and loop > through > all remaining files, extract subsequent data into the next line below. > > Hopefully this is possible and someone can help! > > Thanks > -- > Linda
From: JLatham on 17 Feb 2010 22:28 Or it could be that the .csv file is turning out to be close to a fixed field length file and he means that there's a comma at the 76th and 109th character position in a record? Definitely needs clarification. "KC" wrote: > Interesting exercise. > I am guessing that you have one workbook with one worksheet where > A1, B1 only are filled. Nothing further; > Only search in each csv file for 76th and 109th comma, > In what way is the matching done please? as the following 19 positions are > DIGITS only. > > "L.Mathe" <LMathe(a)discussions.microsoft.com> wrote in message > news:0A02796E-EF3A-4B89-952B-F5BDE3BC2BDC(a)microsoft.com... > >I was looking through this discussion group, found something close to what > >I > > need, but not being a programmer, I haven't been able to modify it to what > > I > > am attempting to do and I hope someone can help. > > > > The .csv files are split into groups by month (ie: "c:\Jan\file > > name.csv)". > > I need to search within the group of csv files and extract data into an > > Excel > > file. What I would like to do is if Cell A1 in my active wb matches the > > data > > to the right of the 76 comma in the csv file, extract the 'text' value > > (must > > be specified as text as this data is a 19 digit number and can't have it > > tuncated), in cell A2. Then in cell B2, extract the data that is to the > > right of the 109 comma. Continue searching the current file and loop > > through > > all remaining files, extract subsequent data into the next line below. > > > > Hopefully this is possible and someone can help! > > > > Thanks > > -- > > Linda > > > . >
From: L.Mathe on 20 Feb 2010 15:38 My apologies for delay in replying, I had the flu and couldn't think straight. I looked more carefully at the type of files I need to seach for a particular string, and found they are 'Excel Comma Separated Values'. The files to be searched average 35,000 lines, and have, 1 believe, 120 columns of data. What I am attempting to do is search the 77th column for matching data, and if there is a match, extract the data in the 47th column (19 digit number, so need to extract as text), and also the data in the 110th column. When opening the file using Note Pad, all the data is enclosed in " " and separated by commas. The workbook I want to extract the data to will always be basically blank. I am hoping to have a user put a 'value' in Cell A1 then use a click button to run the macro. It really doesn't matter what columns data goes to as long as the data extracted is from the same line from the text file. IE Results in WB: Column A Column B 6888551119921316789 01/31/2010 15:10 6888551118195432688 02/13/2010 12:45 The code I found was as follows: 1-Their question: To extract data (the first three letters after the 2nd comma, and the first 35 characters after the 7th comma) from a csv file (over 100,000 rows), only after the 8th column matches a values in column A of my spreadsheet. The two extracted data elements need to be stored in my worksheet in columns B and C. 2- Reply: Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Dim Data(8) 'default folder Folder = "C:\temp" ChDir (Folder) Set fsread = CreateObject("Scripting.FileSystemObject") FName = Application.GetOpenFilename("CSV (*.csv),*.csv") Set fread = fsread.GetFile(FName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) RowCount = 1 Do While tsread.atendofstream = False InputLine = tsread.ReadLine For i = 0 To 7 If InStr(InputLine, ",") > 0 Then Data(i) = Left(InputLine, InStr(InputLine, ",") - 1) InputLine = Mid(InputLine, InStr(InputLine, ",") + 1) Else If Len(InputLine) > 0 Then Data(i) = InputLine InputLine = "" Else Exit For End If End If Next i 'check if 8th item is in column A Set c = Columns("A:A").Find(what:=Data(7), LookIn:=xlValues, _ lookat:=xlWhole) If Not c Is Nothing Then c.Offset(0, 1) = Left(Data(2), 3) c.Offset(0, 2) = Left(Data(7), 35) End If Loop tsread.Close End Sub Unfortunatley I have not been able to modify this (I can hardly read it)! Thanks -- Linda "KC" wrote: > Interesting exercise. > I am guessing that you have one workbook with one worksheet where > A1, B1 only are filled. Nothing further; > Only search in each csv file for 76th and 109th comma, > In what way is the matching done please? as the following 19 positions are > DIGITS only. > > "L.Mathe" <LMathe(a)discussions.microsoft.com> wrote in message > news:0A02796E-EF3A-4B89-952B-F5BDE3BC2BDC(a)microsoft.com... > >I was looking through this discussion group, found something close to what > >I > > need, but not being a programmer, I haven't been able to modify it to what > > I > > am attempting to do and I hope someone can help. > > > > The .csv files are split into groups by month (ie: "c:\Jan\file > > name.csv)". > > I need to search within the group of csv files and extract data into an > > Excel > > file. What I would like to do is if Cell A1 in my active wb matches the > > data > > to the right of the 76 comma in the csv file, extract the 'text' value > > (must > > be specified as text as this data is a 19 digit number and can't have it > > tuncated), in cell A2. Then in cell B2, extract the data that is to the > > right of the 109 comma. Continue searching the current file and loop > > through > > all remaining files, extract subsequent data into the next line below. > > > > Hopefully this is possible and someone can help! > > > > Thanks > > -- > > Linda > > > . >
From: joel on 20 Feb 2010 16:47
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] |