Help Importing Text into Access Table 2

[Accel45]

I have a TXT file containing hundreds of “notes”. The TXT file does not have column headings. Some of the notes are short some are long. The only consistent pattern is that each note begins with “**”.

I would like to import each “note” into a separate record with a table.

I have tried using import wizard but the wizard wants to import each line of the notes as separate records.

Any help would be appreciated.

Accel45

 

[dhookom]

You really need to provide some sample text from the file and how you want it to appear in the table.

The solution would depend on if this is a one time task or requires automation because it is performed periodically.

This might be very complex to the point where you should open in a word processing app first to replace the ** with paragraph marks or similar.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

You can use something like this:

Dim aryTemp() As String
Dim strInputData As String
Dim i As Integer

Open "C:\TextFile.txt" For Input As #1
strInputData = Input$(LOF(1), 1)
Close #1
aryTemp = Split(strInputData, "**")

For i = LBound(aryTemp) to UBound(aryTemp)
    Insert into your table aryTemp(i)
Next i

Code not tested, just the idea....

Have fun.

---- Andy

 

[Accel45]

Using this thread to illustrate content of the text file. I have three notes here but the actual file has hundreds of notes:
---

**
I have a TXT file containing hundreds of "notes". The TXT file does not have column headings. Some of the notes are short some are long. The only consistent pattern is that each note begins with "**".

I would like to import each "note" into a separate record with a table.

I have tried using import wizard but the wizard wants to import each line of the notes as separate records.

Any help would be appreciated.

Accel45

**
You really need to provide some sample text from the file and how you want it to appear in the table.

The solution would depend on if this is a one time task or requires automation because it is performed periodically.

This might be very complex to the point where you should open in a word processing app first to replace the ** with paragraph marks or similar.

**
The North Koreans had been saying for weeks that they would launch a rocket. The international community tried to discourage the launch, but it took place early Friday morning and ended in embarrassment for Pyongyang when it split apart a few minutes after take-off and its debris rained down into the Yellow Sea.

---

The table I want to import the note into:

tblNotes
NoteCategory 'Text
NoteText 'Memo

The imported notes would go to NoteText.
NoteCategory will be populated later upon review of the note.

 

[Andrzejek]

What dhookom wanted to see, I would guess, would be something like:

[tt]
** This is a note. Blah blah blah ** this could be a second note ** some other information here ** End note here
[/tt]
Is that how your text file looks like?

Show us at leasr 3 to 5 Notes from your text file. Just copy/paste.

Have fun.

---- Andy

 

[Accel45]

The actual text is confidential. What I provided above is how the text is formatted in the text file.

 

[Andrzejek]

The context of the text is not important. And you just showed 1 note, right? That's not an example.

Did you try my suggestion (my code example)?

Have fun.

---- Andy

 

[Accel45]

My example above shows three notes:

**
some text that is the note
**
some text that is the second note
**
some text that is the third note


I did not try your suggestion as I did not know how to complete the code to insert aryTemp(i) into table.

btw thank you for helping me with this.

 

[dhookom]

Your code might look like the following. I'll trust Andy's code which looks like a very workable solution.

Dim strSQL as String
Dim aryTemp() As String
Dim strInputData As String
Dim i As Integer

Open "C:\TextFile.txt" For Input As #1
strInputData = Input$(LOF(1), 1)
Close #1
aryTemp = Split(strInputData, "**")


For i = LBound(aryTemp) to UBound(aryTemp)
    strSQL = "Insert into tblNotes (NoteText) Values(""" &  aryTemp(i) & """)"
    debug.Print strSQL
    currentdb.execute strSQL, dbFailOnerror
Next i

Duane
Hook'D on Access
MS Access MVP

 

[Accel45]

Works fine dhookom. Thank you.
And Thank you to Andrzejek.

On question, when running the code stops when a note contains a ' or - or some other non letter characters.

Not that big of a deal as the error message identifies the word containing the character.

Thanks again, for saving me a lot of time.

 

[Andrzejek]

I should really copyright my code....

Dim strSQL as String
Dim aryTemp() As String
Dim strInputData As String
Dim i As Integer

Open "C:\TextFile.txt" For Input As #1
strInputData = Input$(LOF(1), 1)
Close #1
aryTemp = Split(strInputData, "**")


For i = LBound(aryTemp) to UBound(aryTemp)
    strSQL = "Insert into tblNotes (NoteText) Values('" & [blue]Replace([/blue]aryTemp(i)[blue], "'", "''")[/blue] & "')"
    debug.Print strSQL
    currentdb.execute strSQL, dbFailOnerror
Next i

BLUE part will take care of single quote in your note.

Have fun.

---- Andy

 

[dhookom]

I don't deserve a star for this. Andy did all of the heavy lifting. I just filled in the easy stuff.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

From the OP:

each note begins with "**"

Which would suggest your text file looks like:
[tt]
**some text that is the note**some text that is the second note**some text that is the third note
[/tt]

And then a little different sample:
[tt]
**
some text that is the note
**
some text that is the second note
**
some text that is the third note
[/tt]
Which looks to me that each note begins with "**" and a <cr>, which may be vbNewLine. You may want to include <cr> in your Split. Otherwise you will get an empty line at the beginning of your note.

dhookom - thanks for your last post, I appreciate it.

Have fun.

---- Andy