How can I import this data?

[Paul H]

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

[Anthony England]

"Paul H" <no****@nospam.com> wrote in message
news:sV***************@newsfe3-win.ntli.net...

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

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?

[Paul H]

"Anthony England" <ae******@oops.co.uk> wrote in message
news:7O******************************@bt.com...

"Paul H" <no****@nospam.com> wrote in message
news:sV***************@newsfe3-win.ntli.net...

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

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?

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

[Lyle Fairfield]

"Paul H" <no****@nospam.com> wrote in
news:sV***************@newsfe3-win.ntli.net:

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.

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

[Anthony England]

"Paul H" <no****@nospam.com> wrote in message
news:c2*****************@newsfe2-win.ntli.net...

"Anthony England" <ae******@oops.co.uk> wrote in message
news:7O******************************@bt.com...

"Paul H" <no****@nospam.com> wrote in message
news:sV***************@newsfe3-win.ntli.net...

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

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?

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

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(strPath 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(strRecord As String)

Dim lngCusID As Long
Dim strNotes As String

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

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

End Sub

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

[Anthony England]

> 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.
<snip> Lyle Fairfield

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

[Lyle Fairfield]

"Anthony England" <ae******@oops.co.uk> wrote in
news:Hr********************@bt.com:

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

Does this have something to do with nulls?

--
Lyle Fairfield

[Anthony England]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119...

"Anthony England" <ae******@oops.co.uk> wrote in
news:Hr********************@bt.com:

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

Does this have something to do with nulls?

--
Lyle Fairfield

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!

[Lyle Fairfield]

"Anthony England" <ae******@oops.co.uk> wrote in
news:pN******************************@bt.com:

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!

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

[Anthony England]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119...

"Anthony England" <ae******@oops.co.uk> wrote in
news:pN******************************@bt.com:

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!

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

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.

[Paul H]

"Anthony England" <ae******@oops.co.uk> wrote in message
news:pN******************************@bt.com...

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:Xn*********************************@216.221.8 1.119...

"Anthony England" <ae******@oops.co.uk> wrote in
news:Hr********************@bt.com:

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

Does this have something to do with nulls?

--
Lyle Fairfield

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!

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

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:
: "Anthony England" <ae******@oops.co.uk> wrote in
: news:pN******************************@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.listcount' 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

[Anthony England]

> 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

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.

[Anthony England]

> 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.listcount' 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

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.

[Lyle Fairfield]

Thelma Lubkin <th****@alpha2.csd.uwm.edu> wrote in
news:e2**********@uwm.edu:

But when does one cross the 'gratitude line'?

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

[Bob Quintal]

"Anthony England" <ae******@oops.co.uk> wrote in
news:T4********************@bt.com:

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.

I interpreted "one or more persons of your sexual preference" as to
mean "one or more persons of the sex you prefer"

As Lyle and I both hail from Canada, eh, I suspect that the same
phrase may be interpreted differently on the far side of the pond.

--
Bob Quintal

PA is y I've altered my email address.

[Anthony England]

"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...

"Anthony England" <ae******@oops.co.uk> wrote in
news:T4********************@bt.com:

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.

I interpreted "one or more persons of your sexual preference" as to
mean "one or more persons of the sex you prefer"

As Lyle and I both hail from Canada, eh, I suspect that the same
phrase may be interpreted differently on the far side of the pond.

--
Bob Quintal

OK - I confess. It was a deliberate mis-interpretation solely for the
purposes of comic effect.

[Paul H]

"Anthony England" <ae******@oops.co.uk> wrote in message
news:JJ******************************@bt.com...

"Paul H" <no****@nospam.com> wrote in message
news:c2*****************@newsfe2-win.ntli.net...

"Anthony England" <ae******@oops.co.uk> wrote in message
news:7O******************************@bt.com...

"Paul H" <no****@nospam.com> wrote in message
news:sV***************@newsfe3-win.ntli.net...
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
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?

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

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(strPath 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(strRecord As String)

Dim lngCusID As Long
Dim strNotes As String

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

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

End Sub

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

Anthony,

Many thanks for the code. I have tweaked it so that it now imports into a
table. The trouble is, the source data is all over the place.

I need to amend the line:

If strLine Like "####" & Chr$(9) & "*" Then

To account for the fact that some lines do not have a tab!! They seem to
have a carriage return, eg:

CustomerID Notes
0001 Likes chocolate Hates cheese
0002 Likes Apples Hates Pineapple
0003
0004
0005 Smells of Elderberries
0006 etc..

So I tried:

If (strLine Like "####" & Chr$(9) & "*") _
Or (strLine Like "####" & Chr$(13) & Chr$(10))Then

But it still ignores those lines. What ASCII chr should I be looking for?

Thanks,

Paul

[alihelp]

LOAD DATA LOCAL INFILE '//server1/websites/southernphone/news/websites/uploads/email.txt' REPLACE INTO TABLE `tbl_newsletter` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'