Fril
asked on
data import - html tables -> access
Hello everyone,
Firstly, apologies if this has been asked before, I'm guessing it may be a common question, but i've searched around and can't find any specific answers..
I have an extremely large HTML document (around 1000 tables) that i would like to import into an existing table into an access database, but i'm having a few problems...[unfortunately I have to use MS Access.]
the html table lists its data vertically.. so the table headings are horizontal to the data.. eg.
<html tables>
primary key: 00001
address: blah
postcode: blah001
as opposed to the access database, where the table headings are spread horizontally...
<access database>
primary key address postcode
00001 blah blah01
(apologies if the formatting messes up there..)
i've painfully changed all of the table headings inside the html doc, so they are an exact match of the access database.. however when i go to import, the database, it only imports one HTML table, and lists all of the data in the first column of the access table. to throw a spanner in the works, some of the columns..[well rows] in the HTML document will not need to be imported, as the data is not necessary..
Does anyone know any ways around this? I'm guessing one of the following may be applicable;
1. to flip/pivot the HTML tables into the same format, before import? (probably a cpu heavy process)
2. convert the html doc to xml somehow? and then import
3. make access see how the data should be going in, and make it do some thinking and processing of its own? (could be dangerous!)
4. throw the machine out of the window and deny all knowledge of any 'databases'?
unfortunately, the ppl who gave me the html doc are unable to provide it to me in xml form :( i've begged
it would be great if an answer didn't include me having to script to much.. i'm familiar with oracle dbs, pl/sql .. but access isn't my strong point
If anyone could point me to any links where people have had similar problems, or has any specific advice, it would be greatly appreciated!
Thanks in advance!!
Phil
Firstly, apologies if this has been asked before, I'm guessing it may be a common question, but i've searched around and can't find any specific answers..
I have an extremely large HTML document (around 1000 tables) that i would like to import into an existing table into an access database, but i'm having a few problems...[unfortunately I have to use MS Access.]
the html table lists its data vertically.. so the table headings are horizontal to the data.. eg.
<html tables>
primary key: 00001
address: blah
postcode: blah001
as opposed to the access database, where the table headings are spread horizontally...
<access database>
primary key address postcode
00001 blah blah01
(apologies if the formatting messes up there..)
i've painfully changed all of the table headings inside the html doc, so they are an exact match of the access database.. however when i go to import, the database, it only imports one HTML table, and lists all of the data in the first column of the access table. to throw a spanner in the works, some of the columns..[well rows] in the HTML document will not need to be imported, as the data is not necessary..
Does anyone know any ways around this? I'm guessing one of the following may be applicable;
1. to flip/pivot the HTML tables into the same format, before import? (probably a cpu heavy process)
2. convert the html doc to xml somehow? and then import
3. make access see how the data should be going in, and make it do some thinking and processing of its own? (could be dangerous!)
4. throw the machine out of the window and deny all knowledge of any 'databases'?
unfortunately, the ppl who gave me the html doc are unable to provide it to me in xml form :( i've begged
it would be great if an answer didn't include me having to script to much.. i'm familiar with oracle dbs, pl/sql .. but access isn't my strong point
If anyone could point me to any links where people have had similar problems, or has any specific advice, it would be greatly appreciated!
Thanks in advance!!
Phil
Hi Fril,
Have you tried importing the html file into XL first and saving as a spreadsheet which Access will handle easily.
Pete
Have you tried importing the html file into XL first and saving as a spreadsheet which Access will handle easily.
Pete
ASKER
Hi pete,
i never thought of that.. thanks
i've just tried it however, and the data is still the 'wrong way round' headings on the left, data on the right. instead of headings above, data below
You know of any way to flip/rotate/pivot this information automatically? at least it should be easier for me to manipulate in excel.
Thanks for your quick response.
Phil
i never thought of that.. thanks
i've just tried it however, and the data is still the 'wrong way round' headings on the left, data on the right. instead of headings above, data below
You know of any way to flip/rotate/pivot this information automatically? at least it should be easier for me to manipulate in excel.
Thanks for your quick response.
Phil
Fril,
How would you tell (a program) where each record ended?
Pete
How would you tell (a program) where each record ended?
Pete
another way to do this is to save the content of the page as a text file, parse and saved recordset to the
access table.
sample
https://www.experts-exchange.com/questions/21312509/To-Export-URL-Data-Into-Table.html
if you are interested post the URL here, and what information you want extracted and place in a table.
access table.
sample
https://www.experts-exchange.com/questions/21312509/To-Export-URL-Data-Into-Table.html
if you are interested post the URL here, and what information you want extracted and place in a table.
After you import into Excel, then import into Access, you can use a crosstab query to transform the data. The crosstab query wizard should help you do that. You can then create a make table query to take the output of the crosstab query to a new table.
I've sent an email to jjafferr at yahoo dot com to look at your question, he likes these problems.
I've sent an email to jjafferr at yahoo dot com to look at your question, he likes these problems.
I would create a table with an autonumber and one text field then just pull the whole html file in line by line as if it were a text file.
Then after it is in parse the table. This is the outline of a function to parse it just put it into a module and edit it to work for you. It should work quickly and efficiently. I've had to do this kind of thing before.
--------------------------
Public Function ParseTable()
'Declaring variables
Dim DB As Database
Dim InRS As Recordset
Dim OutRS As Recordset
Dim SQL As String
Dim PrimaryKey As String
Dim Address As String
Dim PostCode As String
'Set up to open the input table.
SQL = "SELECT Index_Num, Text_Field " & _
"FROM Import_Table " & _
"ORDER BY Index_Num "
Set DB = CurrentDb()
Set InRS = DB.OpenRecordset(SQL) 'actually open the import table
Set OutRS = DB.OpenRecordset("DataTabl
PrimaryKey = "BLANK1" 'Set the primary key variable to something
'so the first pass gets skipped writing output
If InRS.EOF = False Then InRS.MoveFirst 'move to the first record for input
Do Until InRS.EOF = True
'On each pass it will check if it is seeing the primary key and then it knows _
to write the data in the variables and clear them for the next pass.
If InStr(1, InRS!text_field, "primary key:", vbTextCompare) <> 0 _
And PrimaryKey <> "BLANK1" Then
With OutRS
.AddNew
!Primary_Key = PrimaryKey
!Address = Address
!Post_Code = PostCode
.Update
End With
PrimaryKey = ""
Address = ""
PostCode = ""
End If
'If the input field has "primary key:" in the text then assign it to the variable _
and move onto the next record. and so on and so forth.
If InStr(1, InRS!text_field, "primary key:", vbTextCompare) <> 0 Then
PrimaryKey = Mid(InRS!text_field, InStr(1, InRS!text_field, "primary key:", vbTextCompare), 500)
InRS.MoveNext
End If
If InStr(1, InRS!text_field, "Address:", vbTextCompare) <> 0 Then
PrimaryKey = Mid(InRS!text_field, InStr(1, InRS!text_field, "Address:", vbTextCompare), 500)
InRS.MoveNext
End If
If InStr(1, InRS!text_field, "PostCode:", vbTextCompare) <> 0 Then
PrimaryKey = Mid(InRS!text_field, InStr(1, InRS!text_field, "PostCode:", vbTextCompare), 500)
InRS.MoveNext
End If
InRS.MoveNext
Loop
Set InRS = Nothing
Set OutRS = Nothing
Set DB = Nothing
End Function
Then after it is in parse the table. This is the outline of a function to parse it just put it into a module and edit it to work for you. It should work quickly and efficiently. I've had to do this kind of thing before.
--------------------------
Public Function ParseTable()
'Declaring variables
Dim DB As Database
Dim InRS As Recordset
Dim OutRS As Recordset
Dim SQL As String
Dim PrimaryKey As String
Dim Address As String
Dim PostCode As String
'Set up to open the input table.
SQL = "SELECT Index_Num, Text_Field " & _
"FROM Import_Table " & _
"ORDER BY Index_Num "
Set DB = CurrentDb()
Set InRS = DB.OpenRecordset(SQL) 'actually open the import table
Set OutRS = DB.OpenRecordset("DataTabl
PrimaryKey = "BLANK1" 'Set the primary key variable to something
'so the first pass gets skipped writing output
If InRS.EOF = False Then InRS.MoveFirst 'move to the first record for input
Do Until InRS.EOF = True
'On each pass it will check if it is seeing the primary key and then it knows _
to write the data in the variables and clear them for the next pass.
If InStr(1, InRS!text_field, "primary key:", vbTextCompare) <> 0 _
And PrimaryKey <> "BLANK1" Then
With OutRS
.AddNew
!Primary_Key = PrimaryKey
!Address = Address
!Post_Code = PostCode
.Update
End With
PrimaryKey = ""
Address = ""
PostCode = ""
End If
'If the input field has "primary key:" in the text then assign it to the variable _
and move onto the next record. and so on and so forth.
If InStr(1, InRS!text_field, "primary key:", vbTextCompare) <> 0 Then
PrimaryKey = Mid(InRS!text_field, InStr(1, InRS!text_field, "primary key:", vbTextCompare), 500)
InRS.MoveNext
End If
If InStr(1, InRS!text_field, "Address:", vbTextCompare) <> 0 Then
PrimaryKey = Mid(InRS!text_field, InStr(1, InRS!text_field, "Address:", vbTextCompare), 500)
InRS.MoveNext
End If
If InStr(1, InRS!text_field, "PostCode:", vbTextCompare) <> 0 Then
PrimaryKey = Mid(InRS!text_field, InStr(1, InRS!text_field, "PostCode:", vbTextCompare), 500)
InRS.MoveNext
End If
InRS.MoveNext
Loop
Set InRS = Nothing
Set OutRS = Nothing
Set DB = Nothing
End Function
ASKER
thanks for your responses, sorry i've been away for a few hrs...
pete: the data is seperated within the excel document by 2 clear rows.. (<br><br> in the html)
capricorn: thanks for your suggestion, unfortunately the data is 'sensitive' so i can't really publish it :(
thenelson: seems like a nice solution, and it is able to import one [html] table correctly, however, when i attempt to pull in loads of [html]tables, it likes to change lots of my data to numbers.. and only lists things in the first column.. if you could expand on that, it would be great! - i'll get some more specific information for you in a minute (it may just be the way i'm performing the crosstab query)
jimpen: let me check that and get back to you..
pete: the data is seperated within the excel document by 2 clear rows.. (<br><br> in the html)
capricorn: thanks for your suggestion, unfortunately the data is 'sensitive' so i can't really publish it :(
thenelson: seems like a nice solution, and it is able to import one [html] table correctly, however, when i attempt to pull in loads of [html]tables, it likes to change lots of my data to numbers.. and only lists things in the first column.. if you could expand on that, it would be great! - i'll get some more specific information for you in a minute (it may just be the way i'm performing the crosstab query)
jimpen: let me check that and get back to you..
Hey Nelson, thanks for the email pointing to this Q, I appreciate that :o)
Fril
The best way to do this is to save the HTML page as text, then extract the data with Mid() command,
if you look at the following link, its similar to what you are asking:
https://www.experts-exchange.com/questions/20940488/Parse-emails-from-Outlook-Express.html
Your case would be easier, as you would deal with the text file right away.
and if you don't want to import any part of the text file, don't include it in the if statement.
It would help greatly if you could post here a few lines of your document, OR if there was any way to get the whole document, then nothing like it.
jaffer
Fril
The best way to do this is to save the HTML page as text, then extract the data with Mid() command,
if you look at the following link, its similar to what you are asking:
https://www.experts-exchange.com/questions/20940488/Parse-emails-from-Outlook-Express.html
Your case would be easier, as you would deal with the text file right away.
and if you don't want to import any part of the text file, don't include it in the if statement.
It would help greatly if you could post here a few lines of your document, OR if there was any way to get the whole document, then nothing like it.
jaffer
Your code will look something like this:
Open "C:\DeleteMe.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
DoCmd.GoToRecord , "frmPersonal_CVs", acNewRec
Line Input #1, TextLine ' Read line into variable.
If Mid(TextLine, 1, 5) = "post:" Then Forms![frmPersonal_CVs]![p
If Mid(TextLine, 1, 5) = "name:" Then Forms![frmPersonal_CVs]![n
If Mid(TextLine, 1, 7) = "gender:" Then Forms![frmPersonal_CVs]![g
If Mid(TextLine, 1, 8) = "dob_day:" Then Forms![frmPersonal_CVs]![d
If Mid(TextLine, 1, 10) = "dob_month:" Then Forms![frmPersonal_CVs]![d
If Mid(TextLine, 1, 9) = "dob_year:" Then Forms![frmPersonal_CVs]![d
Loop
Close #1
jaffer
Open "C:\DeleteMe.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
DoCmd.GoToRecord , "frmPersonal_CVs", acNewRec
Line Input #1, TextLine ' Read line into variable.
If Mid(TextLine, 1, 5) = "post:" Then Forms![frmPersonal_CVs]![p
If Mid(TextLine, 1, 5) = "name:" Then Forms![frmPersonal_CVs]![n
If Mid(TextLine, 1, 7) = "gender:" Then Forms![frmPersonal_CVs]![g
If Mid(TextLine, 1, 8) = "dob_day:" Then Forms![frmPersonal_CVs]![d
If Mid(TextLine, 1, 10) = "dob_month:" Then Forms![frmPersonal_CVs]![d
If Mid(TextLine, 1, 9) = "dob_year:" Then Forms![frmPersonal_CVs]
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks to everyone for all your responses and help on this..
I used jjaffers script, which worked a treat!
the only issue i had, were some of the fields were blank, and i had to remove the allow zero length, and some of the dates had been put in an incorrect format, so had to remove the date function.. apart from that, it was a nice easy process thanks to the code you gave..
thanks again!
Phil
I used jjaffers script, which worked a treat!
the only issue i had, were some of the fields were blank, and i had to remove the allow zero length, and some of the dates had been put in an incorrect format, so had to remove the date function.. apart from that, it was a nice easy process thanks to the code you gave..
thanks again!
Phil
ASKER
Well.. someone else in the business has found out about "my" immense skills (i gave proper acknoledgemnts :) converting vertical to horizontal data, and they had a load of column headings that they wanted converting into row headings.. basically what i've just done, except from horizontal to vertical.. there is no data in the tables concerned, and i found this a nice easy solution without the need for vb programming.. (just thought i'd include it on this Q as a reference.. seems a fairly useful point)
CONVERTING COLUMN HEADINGS TO ROW HEADINGS - DOES NOT WORK WITH DATA
1. copy and paste the table into word -
2. in word, highlight the table and click table / convert / table to text
3. Choose seperate text with PARAGRAPH MARKS
4. watch as your data is neatly transformed from horizontal to vertical.
i found a little glitch which basically causes word to avoid applying formatting on any rows greater than 62. meaning, only 62 column headings can be converted at any one time.. although surely thats better than one at a time with copy and paste :)
below is the expert exchange Q this was shamelessly butchered from
https://www.experts-exchange.com/questions/20273540/Excel-Spread-column-data-across-rows.html
CONVERTING COLUMN HEADINGS TO ROW HEADINGS - DOES NOT WORK WITH DATA
1. copy and paste the table into word -
2. in word, highlight the table and click table / convert / table to text
3. Choose seperate text with PARAGRAPH MARKS
4. watch as your data is neatly transformed from horizontal to vertical.
i found a little glitch which basically causes word to avoid applying formatting on any rows greater than 62. meaning, only 62 column headings can be converted at any one time.. although surely thats better than one at a time with copy and paste :)
below is the expert exchange Q this was shamelessly butchered from
https://www.experts-exchange.com/questions/20273540/Excel-Spread-column-data-across-rows.html
Hi Fril
I am glad my code worked for you, and we are always for the lookout for easier and nicer ways, but it is fun to code ;o)
Thanks for the points and the grade.
jaffer
I am glad my code worked for you, and we are always for the lookout for easier and nicer ways, but it is fun to code ;o)
Thanks for the points and the grade.
jaffer
ASKER
no problem jaffer, appreciate the time and effort you put into this, saved me a nightmare data entry job :)
.. agreed coding is fun, you would have liked to see my 2700 records, with all the data 'magically' converting into the format you want.... and its made me want to learn a little VB :) ..
.. agreed coding is fun, you would have liked to see my 2700 records, with all the data 'magically' converting into the format you want.... and its made me want to learn a little VB :) ..
<<but it is fun to code>>
<<agreed coding is fun>>
YOU GUYS ARE WARPED!!!
But not me, I'm the only sane person I know.
<<agreed coding is fun>>
YOU GUYS ARE WARPED!!!
But not me, I'm the only sane person I know.
Some coding is fun. When you can see the definite target it is easy to whip out some code that will work quickly and easily.
> you would have liked to see my 2700 records, with all the data 'magically' converting into the format you want
Ah, you should have said so, I would insert this line of code in the middle of any IF statement:
MyCount=MyCount+1
Call SysCmd(acSysCmdSetStatus, "Records Entered in the Table: " & MyCount)
This would show you the Records Entered in the Table, this will be displayed on your status Bar (at the bottom of your Form screen), I am sure
http://en.wikipedia.org/wiki/Count_von_Count
would have done the same, but louder ;o)
jaffer
Ah, you should have said so, I would insert this line of code in the middle of any IF statement:
MyCount=MyCount+1
Call SysCmd(acSysCmdSetStatus, "Records Entered in the Table: " & MyCount)
This would show you the Records Entered in the Table, this will be displayed on your status Bar (at the bottom of your Form screen), I am sure
http://en.wikipedia.org/wiki/Count_von_Count
would have done the same, but louder ;o)
jaffer
ASKER
primary key address postcode
<existing data> <existing data> <existing data>
0001
blah
blah01