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******** *********@newsf e2-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(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
' *************** *************** ************

[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.81.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.81.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.