How can I import this data?

Re: How can I import this data?


"Paul H" <nospam@nospam. com> wrote in message
news:sV%3g.387$ EM5.106@newsfe3-win.ntli.net...[color=blue]
>I have a CSV file that is tab delimited and has no text qualifiers.
>Normally this would be OK, but the data has tabs in it as well!
>
> Is there anyway I can get this data into access? Here is what the data
> looks like (I have replaced the actual tabs with [TAB] for illustration
> purposes):
>
> CustomerID[TAB]CustomerNotes[TAB][LINEBREAK]
> 0001[TAB]Likes chocolate[TAB][TAB]Hates cheese[TAB][LINEBREAK]
> 0002[TAB][TAB]Likes Apples[TAB][LINEBREAK][TAB]Hates Pineapple[LINEBREAK]
> 0003[TAB]Snores loudly[LINEBREAK][TAB][TAB]Smokes too much
> [TAB][LINEBREAK]
> 0004 etc...
>
> Can I fix this tab delimited data with tabs and line breaks all over the
> place? BTW, I can only get the data in this format i.e. we can not
> manipulate it before we get the CSV file.
>
> Thanks,
>
> Paul[/color]


This sort of thing is what Access programmers often have to do - and to do
it properly you would need to write a custom vba routine. The most
important first question is what table structure do you want it to be put
into? Typically you might use three fields: ID (just an autonumber to
identify the row), CustomerID and Notes, so the new table would be:

0001 0001 Likes chocolate
0002 0001 Hates cheese
0003 0002 Likes Apples
0004 0002 Hates Pineapple

but perhaps you have other requirements.
The second question is: can you write any vba or were you looking for
someone to write the complete routine for you?

Re: How can I import this data?


"Anthony England" <aengland@oops. co.uk> wrote in message
news:7OKdncePRL A_BM3ZnZ2dnUVZ8 s-dnZ2d@bt.com...[color=blue]
>
> "Paul H" <nospam@nospam. com> wrote in message
> news:sV%3g.387$ EM5.106@newsfe3-win.ntli.net...[color=green]
>>I have a CSV file that is tab delimited and has no text qualifiers.
>>Normally this would be OK, but the data has tabs in it as well!
>>
>> Is there anyway I can get this data into access? Here is what the data
>> looks like (I have replaced the actual tabs with [TAB] for illustration
>> purposes):
>>
>> CustomerID[TAB]CustomerNotes[TAB][LINEBREAK]
>> 0001[TAB]Likes chocolate[TAB][TAB]Hates cheese[TAB][LINEBREAK]
>> 0002[TAB][TAB]Likes Apples[TAB][LINEBREAK][TAB]Hates Pineapple[LINEBREAK]
>> 0003[TAB]Snores loudly[LINEBREAK][TAB][TAB]Smokes too much
>> [TAB][LINEBREAK]
>> 0004 etc...
>>
>> Can I fix this tab delimited data with tabs and line breaks all over the
>> place? BTW, I can only get the data in this format i.e. we can not
>> manipulate it before we get the CSV file.
>>
>> Thanks,
>>
>> Paul[/color]
>
>
> This sort of thing is what Access programmers often have to do - and to do
> it properly you would need to write a custom vba routine. The most
> important first question is what table structure do you want it to be put
> into? Typically you might use three fields: ID (just an autonumber to
> identify the row), CustomerID and Notes, so the new table would be:
>
> 0001 0001 Likes chocolate
> 0002 0001 Hates cheese
> 0003 0002 Likes Apples
> 0004 0002 Hates Pineapple
>
> but perhaps you have other requirements.
> The second question is: can you write any vba or were you looking for
> someone to write the complete routine for you?
>[/color]

Thanks Anthony,

Sorry I didn't explain myself very well. It is just two fields; CustomerID
and Notes. it should look like this:

CustomerID Notes
0001 Likes chocolate Hates cheese
0002 Likes Apples Hates Pineapple

The problem is that the Notes data has random Tabs and line breaks in it. I
am comfortable writing VBA but have no idea where to start with this data
because I can not predict where and how many tabs or linebreaks there will
be in each Notes field. I would appreciate any pointers.

Regards,

Paul

Re: How can I import this data?

"Paul H" <nospam@nospam. com> wrote in
news:sV%3g.387$ EM5.106@newsfe3-win.ntli.net:
[color=blue]
> I have a CSV file that is tab delimited and has no text qualifiers.
> Normally this would be OK, but the data has tabs in it as well!
>
> Is there anyway I can get this data into access? Here is what the data
> looks like (I have replaced the actual tabs with [TAB] for
> illustration purposes):
>
> CustomerID[TAB]CustomerNotes[TAB][LINEBREAK]
> 0001[TAB]Likes chocolate[TAB][TAB]Hates cheese[TAB][LINEBREAK]
> 0002[TAB][TAB]Likes Apples[TAB][LINEBREAK][TAB]Hates
> Pineapple[LINEBREAK] 0003[TAB]Snores loudly[LINEBREAK][TAB][TAB]Smokes
> too much [TAB][LINEBREAK] 0004 etc...
>
> Can I fix this tab delimited data with tabs and line breaks all over
> the place? BTW, I can only get the data in this format i.e. we can not
> manipulate it before we get the CSV file.[/color]

It's quite possible to write VBA code that parses and imports this data.
I'm hesitant because of many experiences where the poster responds:

Well, I'm using Access 97 and Replace doesn't work;
Well, that's not what the data are really like; I was just giving an
example and while your solution works on the example, it won't work on
the "real" thing;
Well, the ids actually range from 1 to 100000; I just put the 000?
pattern there so it would look good in the post.


or doesn't respond with:
Well, I've never seen anything like that before so I won't even try it.

If you'd like some possible solutions I suggest that you make some part
of the file available preferable as a download from a web-server, specify
what version of Access you are using and clearly state how we may
identify IDs.

I expect you are already aware from other answers that you db design may
be suspect. A primary rule of db design is that columns be atomic, that
they describe a single attribute. Your notes field is not. This may be
the foundation for your problem, even though it appears that it's a
problem with the CSV file.

Regardless, writing the code you are wondering about is a ten minute job,
when we know the rules and the background.

--
Lyle Fairfield

Re: How can I import this data?


"Paul H" <nospam@nospam. com> wrote in message
news:c224g.2414 $TT2.1956@newsf e2-win.ntli.net...[color=blue]
>
> "Anthony England" <aengland@oops. co.uk> wrote in message
> news:7OKdncePRL A_BM3ZnZ2dnUVZ8 s-dnZ2d@bt.com...[color=green]
>>
>> "Paul H" <nospam@nospam. com> wrote in message
>> news:sV%3g.387$ EM5.106@newsfe3-win.ntli.net...[color=darkred]
>>>I have a CSV file that is tab delimited and has no text qualifiers.
>>>Normally this would be OK, but the data has tabs in it as well!
>>>
>>> Is there anyway I can get this data into access? Here is what the data
>>> looks like (I have replaced the actual tabs with [TAB] for illustration
>>> purposes):
>>>
>>> CustomerID[TAB]CustomerNotes[TAB][LINEBREAK]
>>> 0001[TAB]Likes chocolate[TAB][TAB]Hates cheese[TAB][LINEBREAK]
>>> 0002[TAB][TAB]Likes Apples[TAB][LINEBREAK][TAB]Hates
>>> Pineapple[LINEBREAK]
>>> 0003[TAB]Snores loudly[LINEBREAK][TAB][TAB]Smokes too much
>>> [TAB][LINEBREAK]
>>> 0004 etc...
>>>
>>> Can I fix this tab delimited data with tabs and line breaks all over the
>>> place? BTW, I can only get the data in this format i.e. we can not
>>> manipulate it before we get the CSV file.
>>>
>>> Thanks,
>>>
>>> Paul[/color]
>>
>>
>> This sort of thing is what Access programmers often have to do - and to
>> do it properly you would need to write a custom vba routine. The most
>> important first question is what table structure do you want it to be put
>> into? Typically you might use three fields: ID (just an autonumber to
>> identify the row), CustomerID and Notes, so the new table would be:
>>
>> 0001 0001 Likes chocolate
>> 0002 0001 Hates cheese
>> 0003 0002 Likes Apples
>> 0004 0002 Hates Pineapple
>>
>> but perhaps you have other requirements.
>> The second question is: can you write any vba or were you looking for
>> someone to write the complete routine for you?
>>[/color]
>
> Thanks Anthony,
>
> Sorry I didn't explain myself very well. It is just two fields; CustomerID
> and Notes. it should look like this:
>
> CustomerID Notes
> 0001 Likes chocolate Hates cheese
> 0002 Likes Apples Hates Pineapple
>
> The problem is that the Notes data has random Tabs and line breaks in it.
> I am comfortable writing VBA but have no idea where to start with this
> data because I can not predict where and how many tabs or linebreaks there
> will be in each Notes field. I would appreciate any pointers.
>
> Regards,
>
> Paul[/color]



OK if you are comfortable writing vba then I'll help with the bit you can't
do, and you can do the rest by replacing the WriteRecord routine with code
that genuinely writes out the data rather than simply putting it in the
debug window.
In case any explanation is needed it reads the file line by line and if it
comes to a line which starts with 0002[TAB] then it knows a new record is
beginning.


' *************** *************** ************
' Code Starts
' *************** *************** ************
Option Compare Database
Option Explicit

Public Function DoImport(strPat h As String) As Boolean

On Error GoTo Err_Handler

Dim intFile As Integer
Dim strLine As String
Dim blnOpen As Boolean
Dim strRecord As String

intFile = FreeFile

Open strPath For Input Access Read Lock Write As #intFile

blnOpen = True

If Not EOF(intFile) Then
' Take the first line and ignore it
Line Input #intFile, strLine
End If

While Not EOF(intFile)
Line Input #intFile, strLine
If strLine Like "####" & Chr$(9) & "*" Then
WriteRecord strRecord
strRecord = strLine
Else
strRecord = strRecord & strLine
End If
Wend

WriteRecord strRecord

DoImport = True

Exit_Handler:

If blnOpen Then
Close intFile
End If

Exit Function

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Private Sub WriteRecord(str Record As String)

Dim lngCusID As Long
Dim strNotes As String

If strRecord Like "####" & Chr$(9) & "*" Then
lngCusID = CLng(Left$(strR ecord, 4))
strNotes = Mid$(strRecord, 6)
Else
Exit Sub
End If

Debug.Print Format(lngCusID , "0000") & " " & strNotes

End Sub

' *************** *************** ************
' Code Ends
' *************** *************** ************

Re: How can I import this data?

> It's quite possible to write VBA code that parses and imports this data.[color=blue]
> I'm hesitant because of many experiences where the poster responds:
>
> Well, I'm using Access 97 and Replace doesn't work;
> Well, that's not what the data are really like; I was just giving an
> example and while your solution works on the example, it won't work on
> the "real" thing;
> Well, the ids actually range from 1 to 100000; I just put the 000?
> pattern there so it would look good in the post.
>
>
> or doesn't respond with:
> Well, I've never seen anything like that before so I won't even try it.
>[/color]
<snip>[color=blue]
> Lyle Fairfield[/color]


I'm eagerly anticipating one or more of those responses!
However you have omitted what actually must be the most common response - no
response.

Re: How can I import this data?

"Anthony England" <aengland@oops. co.uk> wrote in
news:HrednTYRhN yEUs3ZRVnygw@bt .com:
[color=blue]
> I'm eagerly anticipating one or more of those responses!
> However you have omitted what actually must be the most common
> response - no response.[/color]

Does this have something to do with nulls?

--
Lyle Fairfield

Re: How can I import this data?

"Lyle Fairfield" <lylefairfield@ aim.com> wrote in message
news:Xns97B26A6 015B3Alylefairf ieldaimcom@216. 221.81.119...[color=blue]
> "Anthony England" <aengland@oops. co.uk> wrote in
> news:HrednTYRhN yEUs3ZRVnygw@bt .com:
>[color=green]
>> I'm eagerly anticipating one or more of those responses!
>> However you have omitted what actually must be the most common
>> response - no response.[/color]
>
> Does this have something to do with nulls?
>
> --
> Lyle Fairfield[/color]


Not connected to the current post, but I genuinely wonder what is with these
people who appear, ask a question, get a very helpful and detailed response
but never appear again. Was the post even read?
I also am genuinely puzzled by my inability to accept that this is the way
it works, and that if you are looking for any response, it would be better
to get this via paid employment where not only are you sure that your work
is seen by others, but you are rewarded with cash - amazing! I need to get
a job!

Re: How can I import this data?

"Anthony England" <aengland@oops. co.uk> wrote in
news:pN-dnTmIVpQIRM3ZnZ 2dnUVZ8qOdnZ2d@ bt.com:
[color=blue]
> Not connected to the current post, but I genuinely wonder what is with
> these people who appear, ask a question, get a very helpful and
> detailed response but never appear again. Was the post even read?
> I also am genuinely puzzled by my inability to accept that this is the
> way it works, and that if you are looking for any response, it would
> be better to get this via paid employment where not only are you sure
> that your work is seen by others, but you are rewarded with cash -
> amazing! I need to get a job![/color]

LOL! I recommend you get one or more persons of your sexual preference and
hole up with them for a few years living on welfare while you consider all
this carefully! When you get to be my age you may wonder whether this job
thing and the dedication to it, and even the economic benefits, were
really, really the best way you could have spent your life.

And gratitude here? Sometimes, but not often. Maybe it's because they
didn't understand your answer, or realized upon reading it that their whole
db is just crap and chose to slink away rather than confront their own work
and level of accomplishment.

Something like I'm doing with ASP.net right now where I say, "Do I really
wanna expend the energy, and experience the frustrations necessary to
become capable with this? Maybe I should just slip over to Woodbine and put
a few shekels on Big Red Underwear in the seventh."

--
Lyle Fairfield

Re: How can I import this data?


"Lyle Fairfield" <lylefairfield@ aim.com> wrote in message
news:Xns97B270F 98F24Elylefairf ieldaimcom@216. 221.81.119...[color=blue]
> "Anthony England" <aengland@oops. co.uk> wrote in
> news:pN-dnTmIVpQIRM3ZnZ 2dnUVZ8qOdnZ2d@ bt.com:
>[color=green]
>> Not connected to the current post, but I genuinely wonder what is with
>> these people who appear, ask a question, get a very helpful and
>> detailed response but never appear again. Was the post even read?
>> I also am genuinely puzzled by my inability to accept that this is the
>> way it works, and that if you are looking for any response, it would
>> be better to get this via paid employment where not only are you sure
>> that your work is seen by others, but you are rewarded with cash -
>> amazing! I need to get a job![/color]
>
> LOL! I recommend you get one or more persons of your sexual preference and
> hole up with them for a few years living on welfare while you consider all
> this carefully! When you get to be my age you may wonder whether this job
> thing and the dedication to it, and even the economic benefits, were
> really, really the best way you could have spent your life.
>
> And gratitude here? Sometimes, but not often. Maybe it's because they
> didn't understand your answer, or realized upon reading it that their
> whole
> db is just crap and chose to slink away rather than confront their own
> work
> and level of accomplishment.
>
> Something like I'm doing with ASP.net right now where I say, "Do I really
> wanna expend the energy, and experience the frustrations necessary to
> become capable with this? Maybe I should just slip over to Woodbine and
> put
> a few shekels on Big Red Underwear in the seventh."
>
> --
> Lyle Fairfield[/color]




I like the holing up idea. Although I wonder if the line about "one or more
persons of your sexual preference". I would prefer to hole up with someone
with the opposite sexual preference - not all Englishmen are gay, you know.

Re: How can I import this data?


"Anthony England" <aengland@oops. co.uk> wrote in message
news:pN-dnTmIVpQIRM3ZnZ 2dnUVZ8qOdnZ2d@ bt.com...[color=blue]
> "Lyle Fairfield" <lylefairfield@ aim.com> wrote in message
> news:Xns97B26A6 015B3Alylefairf ieldaimcom@216. 221.81.119...[color=green]
>> "Anthony England" <aengland@oops. co.uk> wrote in
>> news:HrednTYRhN yEUs3ZRVnygw@bt .com:
>>[color=darkred]
>>> I'm eagerly anticipating one or more of those responses!
>>> However you have omitted what actually must be the most common
>>> response - no response.[/color]
>>
>> Does this have something to do with nulls?
>>
>> --
>> Lyle Fairfield[/color]
>
>
> Not connected to the current post, but I genuinely wonder what is with
> these people who appear, ask a question, get a very helpful and detailed
> response but never appear again. Was the post even read?
> I also am genuinely puzzled by my inability to accept that this is the way
> it works, and that if you are looking for any response, it would be better
> to get this via paid employment where not only are you sure that your work
> is seen by others, but you are rewarded with cash - amazing! I need to
> get a job!
>
>[/color]

Can I take this opportunity to say that I love you both deeply and
profoundly for the assistance you have given me. The app that produced this
data was crap, whereas my app will be a majestic masterpiece.

I will also let you know how I got on so I can thank you some more..

;o)

Cheers chaps

Paul

Re: How can I import this data?

Lyle Fairfield <lylefairfield@ aim.com> wrote:
: "Anthony England" <aengland@oops. co.uk> wrote in
: news:pN-dnTmIVpQIRM3ZnZ 2dnUVZ8qOdnZ2d@ bt.com:

: And gratitude here? Sometimes, but not often. Maybe it's because they
: didn't understand your answer, or realized upon reading it that their whole
: db is just crap and chose to slink away rather than confront their own work
: and level of accomplishment.

But when does one cross the 'gratitude line'?
Of course I need to thank someone for a clear explanation or
an example piece of code, but if I ask something like

'How do I know how many rows my listbox contains?'

and I get the answer 'listbox.listco unt' without even
the obvious 'you could have googled that in 20 seconds', do I
need to post my gratitude? Is that politeness or pollution?
Haven't I just doubled the number of posts on the issue
without adding anything of value?
--thelma
: --
: Lyle Fairfield

Re: How can I import this data?

> Can I take this opportunity to say that I love you both deeply and[color=blue]
> profoundly for the assistance you have given me. The app that produced
> this data was crap, whereas my app will be a majestic masterpiece.
>
> I will also let you know how I got on so I can thank you some more..
>
> ;o)
>
> Cheers chaps
>
> Paul[/color]


Thank you.
I'm sure this will, at least to some extent, make up for the years of
bullying and abuse I suffered at school; both as pupil and teacher.

Re: How can I import this data?

> But when does one cross the 'gratitude line'?[color=blue]
> Of course I need to thank someone for a clear explanation or
> an example piece of code, but if I ask something like
>
> 'How do I know how many rows my listbox contains?'
>
> and I get the answer 'listbox.listco unt' without even
> the obvious 'you could have googled that in 20 seconds', do I
> need to post my gratitude? Is that politeness or pollution?
> Haven't I just doubled the number of posts on the issue
> without adding anything of value?
> --thelma[/color]


Personally speaking, I can think of many worse things to waste bandwidth on
than thanks. If the answer is brief then just "thanks" would do, but it's
not really the thanks I need, but the knowledge that the answer solved the
problem.

Re: How can I import this data?

Thelma Lubkin <thelma@alpha2. csd.uwm.edu> wrote in
news:e2qt4f$phh $1@uwm.edu:
[color=blue]
> But when does one cross the 'gratitude line'?[/color]

I think that a simple acknowledgment indicates to the responder that the
original poster saw the reply and that the solution offered worked, or
didn’t. It reinforces the responder’s self-concept and helps to sharpen his
or her skills of analysis (was the question understood?) and synthesis (did
the answer take into account all the circumstances described?). It may
increase the likelihood that the responder will stir himself or herself to
answer another question from the same person.

In addition a note that an answer is satisfactory may reduce the number of
posts as others reading the thread may realize that the question has been
dealt with satisfactorily and choose to move on rather than to contribute
their own, sometime unique, solution.

And it’s a common courtesy.

--
Lyle Fairfield