Re: Easiest Way to do this Importing method?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: D (d_at_d.com)
Date: 11/10/04 

Date: Tue, 9 Nov 2004 19:44:06 -0700

FYI-
Found the easiest way to do this is via linking the cells. I hi-light a
column of info from the CSV file, then paste it on another *** in the
column I want it to go. Then I click the little clip board and select link
cells. And then I use this whole file as a template for all my work. Then
just save as when I'm done. Fastest, easiest way to do it.
Thanks!
D

"Patrik" <patrikho@hotmail.com> wrote in message
news:60d562f.0411090126.4f786b89@posting.google.com...
> "D" <d@d.com> wrote in message news:<HdXjd.244895$a85.37719@fed1read04>...
>> Hey guys- been searching for a while now, and all the posts are a little
>> more complex than I think this needs to be. Can you help?
>>
>> Basically, I have a CSV file that I need to import into another
>> 'Template'
>> file. But- here's the key- not EVERY column in the CSV file needs to
>> import
>> into my template. Also, the columns in the CSV file are NOT in the same
>> order they need to be in the Template file. So, I need to be able to pick
>> and choose which columns import and go where. Does that make sense? Is
>> there
>> an easy 'import mapping' system to use or any other way to do this? Like
>> I
>> said, everything Im seeing is looking pretty complicated- more than I
>> think
>> it needs to be. This seems as if it'd be fairly easy to accomplish, I'm
>> just
>> not finding answers I need.
>> Thanks for any help.
>> D
>
> Hello
> Well I like programming/VBA approaches because its quick and gives you
> good control. I dont know if there is any "Import wizard" that can do
> this. To write code that does what you want is not very difficult.
> A suggestion would be to read all of the CSV-file into an array and go
> from there. It is easier and more effective to manupulate data "In
> memory".
> Something like this:
> Function CSVFileToArray(inFile As String) As Variant
> Dim fileHandle As Integer
> Dim i As Integer
> Dim outArray() As String
> Dim tempStr As String
> If Dir(inFile) = "" Then 'Check if the file exists
> Msgbox "CSVFileToArray: Could not find/open " & inFile
> Exit Function
> End If
> fileHandle = FreeFile()
> Open inFile For Input As #fileHandle
> i = 0
> Do While Not EOF(fileHandle) ' Loop until end of file.
> On Error Resume Next
> Input #fileHandle, tempStr
> On Error GoTo 0
> If tempStr <> "" And InStr(",", tempStr) = 0 Then
> ReDim Preserve outArray(i)
> outArray(i) = tempStr
> i = i + 1
> End If
> Loop
> Close #fileHandle
> CSVFileToArray = outArray
> End Function
>
> Then you can describe what you want from the file in another array
> that you initialize early on. Here is an example function from a
> program I've done:
>
> Type tFieldDesc
> sFieldName As String
> iColPos As Integer
> bConvert As Boolean
> bDate As Boolean
> End Type
>
> Function CreateFieldDescrArray() As tFieldDesc()
> Static bIsInitialized As Boolean
> Static arrFieldDesc(FIELD_DESC_ARRAY_UBOUND) As tFieldDesc
> 'CAUTION the global constant FIELD_DESC_ARRAY_UBOUND must be set to
> 'the number of field descriptions minus one!
>
> 'An array that contains descriptions of fields
> '0 = The name of the field
> '1 = The column position
> '2 = Does the field need conversion
> '3 = Is it a date field
> If Not bIsInitialized Then
> Dim arrTmp, vElement
> arrTmp = Array( _
> Array("Provid", "1", "", ""), Array("Status", "2", "Y", ""), _
> Array("UPPDRAG", "6", "", ""), Array("Header", "7", "", ""), _
> Array("Bestnamn", "8", "", ""), Array("BestAvd", "9", "", ""), _
> Array("Memoid", "10", "", ""), Array("UtfAvd", "11", "", ""), _
> Array("StartW", "12", "", "Y"), Array("EndW", "13", "", "Y"), _
>
> Dim i As Integer
> i = 0
> For Each vElement In arrTmp
> arrFieldDesc(i).sFieldName = vElement(0)
> arrFieldDesc(i).iColPos = CInt(vElement(1))
> arrFieldDesc(i).bConvert = False
> If vElement(2) <> "" Then
> arrFieldDesc(i).bConvert = True
> End If
> arrFieldDesc(i).bDate = False
> If vElement(3) <> "" Then
> arrFieldDesc(i).bDate = True
> End If
> i = i + 1
> Next
> bIsInitialized = True
> End If
> CreateFieldDescrArray = arrFieldDesc
> End Function
>
> Then you write the main data crunching Sub. Something like this:
>
> Sub GetTheData()
> arrFieldDescr = CreateFieldDescrArray()
> arrTheWholeFile = CSVFileToArray("C:\Datafile.csv")
>
> j = 0
> k = 0
> For i = 0 to Ubound(arrTheWholeFile)
> If j Mod NUMBER_OF_COLUMNS = 0 Then
> 'NUMBER_OF_COLUMNS is a constant that has the number of columns
> in the
> 'CSVfile you wish to process
> j = 0
> else
> j = j + 1
> end if
> sResult = GetElement(arrFieldDescr, arrTheWholeFile(i), j)
> if sResult <> "" then
> arrResult(k) = sResult
> k = k + 1
> end if
> next
> end sub
>
> Function GetElement(arrFieldDescr, sElement, j) as string
> GetElement = ""
> For each item in arrFieldDescr
> if item.iColPos = j then
> GetElement = sElement
> break
> end if
> next
> end function
>
>
> This was a very quick repsonse just to get you started along the way I
> would reccomend. All variables should be declared of course.
>
> Regards /Patrik

Quantcast