Hey everyone. I have a challenge here that I should be able to figure out but I can't. I'm trying to use the VBA script below to import a text flat file into an existing access db table. Problem is, I can't seem to match the table fields to whats coming from the text file. Can anyone give me a hand? Dim MyDB As DAO.Database, MyRec As DAO.Recordset Dim MyList As String Set MyDB = CurrentDb Set MyRec = MyDB.OpenRecordset(''Select * From FlatFileImport'')
Dim filnam As String, s As String, MyLine As String, MyLocation As Long, ch As String Dim once As Boolean
'filnam = ''C:Upload8507CTINC.txt'' filnam = Me![Text2] Set MyRec = MyDB.OpenRecordset(''Select * From FlatFileImport'') once = False If Not IsNull(Me.Text2) Then filnam = Me.Text2 Open filnam For Binary As #1 MyLine = '''' MyLocation = 0 Do While MyLocation < LOF(1) ch = Input(99, #1) If once Then MyRec.AddNew MyRec.Fields(1) = Mid(MyLine, 2, 2) 'MyRec.Fields(2) = Mid(MyLine, 3, 3) MyRec.Fields(3) = Mid(MyLine, 6, 2) MyRec.Fields(4) = Mid(MyLine, 8, 13) 'MyRec.Fields(5) = Mid(MyLine, 21, 5) 'MyRec.Fields(6) = Mid(MyLine, 26, 15) 'MyRec.Fields(7) = Mid(MyLine, 48, 6) 'MyRec.Fields(7) = '''' & Mid(MyLine, 48, 2) & ''/'' & Mid(MyLine, 50, 2) & ''/'' & Mid(MyLine, 52, 2) & '''' 'MyRec.Fields(8) = Mid(MyLine, 55, 5) 'MyRec.Fields(9) = Mid(MyLine, 62, 4) 'MyRec.Fields(10) = Mid(MyLine, 66, 5) MyRec.Fields(11) = Mid(MyLine, 8, 8) End If
once = True MyLine = "" MyLine = MyLine & ch MyLocation = Loc(1) Loop
MsgBox " File has been successfully imported! " Close #1 ' Close file. rs.Close
'close and erase the file from memory objText.Close Set objText = Nothing
Set rs = Nothing
- If you need clarification, ask it in the comment box above.
- Better answers use proper spelling and grammar.
- Provide details, support with references or personal experience.
Tell us some more! Your answer needs to include more details to help people.You can't post answers that contain an email address.Please enter a valid email address.The email address entered is already associated to an account.Login to postPlease use English characters only.
Tip: The max point reward for answering a question is 15.
You can import address book contacts from other Microsoft Windows® Address Book files (.wab), as well as from Netscape Communicator, Microsoft Exchange Personal Address Book, or any text (.csv) file.
For Windows Address Book
1.
On the File menu, point to Import, and then click Address Book.
2.
Select the address book or file type you want to import, and then click Open.
For All Other Address Book Formats
1.
On the File menu, point to Import, and then click Other Address Book.
2.
Click the address book or file type you want to import, and then click Open.
If your address book is not listed, you can export it to either a text (.csv) file or a Lightweight Directory Access Protocol (LDAP) or Lightweight Directory Interchange Format (LDIF) file, and then import it using that file type.
ou can try to use third party Access recovery utility to restore your lost and damaged data from .mdb files. For more details about this software:- Access File Recovery
Your code does not save in access format it just makes a copy of existing file. You need to open table by table and export data using csv format.
It can be done using access OLE to export, or
write some VB code to access the database tables via ODBC and save row data to csv file
severals solution : - save file as text separated by comma and use sqloader from oracle - use function in excel to concatenate insert order : "insert into TABLE_NAME (field1,...,fieldn) values (A1,B1,...,??n);". Save file as text with sql extension. Connect against roacle db and execute file previously saved. - use ODBC from MicroSoft
The way to get the database size is to point to the directory that the database is do a DIR statement with an output to a file as in DIR payroll.mdb>dbsize.txt and then read from the text file, the first field of the second line being the name (which you really don't need) then the size value of the database.
To import the text files using VB 8 use
'Imports System.Data.OleDb
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\path\filename.mdb")
Dim cmd As New OleDbCommand("SELECT * INTO [import] FROM [Text;Database=d:\path;Hdr=No].[dbsize.txt]", conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Assuming that you are not able to export the data from the original point of sale application, which is obviously the best choice...
1) Try to open the file(s) with Microsoft Excel (make sure that all of Excel's input conversion filters have been installed and enabled).
Most database files are organized as a set of (nested) two dimensional data tables, which can be individually imported into Excel as spread sheets.
Excel is very good at detecting and converting database tables. Other spread sheets such as Lotus 123, or Quatro pro may also be able to do this.
2) Microsoft Access may also be capable of importing and converting the entire database.
3) Heavy duty data-base modeling suites, such as ER-Win by Computer Associates (CA) may also be able to import and export the database.
4) If the files are in the open *.dbf format, then I have the binary file specifications, which can be used to decipher the files.
5) The files may also be simple tables, which are TAB, SPACE, or COMMA (etc.) delimited, and you should be able to recover these with a simple notepad editor or uuedit.
6) Most DOS programs (not all) can also be run under Windows XP, using the command prompt:
START ==> All programs ==> Accessories ==> Command Prompt.
======================================== Most importantly, we need to know the name of the application that created the files, or see a sample of the file, to give you further advice.
Import Microsoft Office documents (Windows only)
You can insert the full contents of a Microsoft Word or Excel document in a new or existing web page. When you import a Word or Excel document, Dreamweaver receives the converted HTML and inserts it into your web page. The file’s size, after Dreamweaver receives the converted HTML, must be less than 300K.
Instead of importing the entire contents of a file, you can also paste portions of a Word document and preserve the formatting. Note: If you use Microsoft Office 97, you cannot import the contents of a Word or Excel document; you must insert a link to the document.
Open the web page into which you want to insert the Word or Excel document.
In Design view, do one of the following to select the file:
Drag the file from its current location to the page where you want the content to appear.
Select File > Import > Word Document or File > Import > Excel Document.
In the Insert Document dialog box, browse to the file you want to add, select any of the formatting options from the Formatting pop-up menu at the bottom of the dialog box, and then click Open.
Text Only
Inserts unformatted text. If the original text is formatted, all formatting will be removed.
Text With Structure
Inserts text that retains structure, but does not retain basic formatting. For example, you can paste text and retain the structure of paragraphs, lists, and tables, without retaining bold, italics, and other formatting.
Text With Structure Plus Basic Formatting
Inserts both structured and simple HTML-formatted text (e.g., paragraphs and tables, as well as text formatted with the b, i, u, strong, em, hr, abbr, or acronym tag).
Text With Structure Plus Full Formatting
Inserts text that retains all structure, HTML formatting, and CSS styles.
Clean Up Word Paragraph Spacing
Eliminates extra space between paragraphs when you paste your text if you selected Text With Structure or Basic Formatting.
The contents of the Word or Excel document appear in your page.
I could not understand the 1st question clearly. as for the second question you can download microsoft viewers for any office product at their site. they can view files only.
×