From: L.Mathe on
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
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
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
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

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=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]