Question about Microsoft Access 2003 for PC

1 Answer

Unable to import text file to access table

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.
MyRec.Close
Set MyRec = Nothing

Posted by on

Ad

1 Answer

  • Level 1:

    An expert who has achieved level 1.

    Problem Solver:

    An expert who has answered 5 questions.

  • Contributor
  • 1 Answer

Hey everyone, I was able to fix my problem using the following:

filnam = Me![Text2]
Set objFile = CreateObject("Scripting.FileSystemObject")
Set objText = objFile.OpenTextFile(filnam)
Set rs = CurrentDb.OpenRecordset("FlatFileImport")
once = False
If Not IsNull(Me.Text2) Then filnam = Me.Text2
Open filnam For Binary As #1
Do While Not objText.AtEndOfStream
strTextLine = objText.Readline
Data = Split(strTextLine, ",")
If once Then
rs.AddNew
rs.Fields(1) = Data(0) 'Site ID
rs.Fields(2) = Data(1) 'Client ID
rs.Fields(3) = Data(2) 'Client Name
rs.Fields(4) = Data(3) 'DDA Account
rs.Fields(5) = Data(4) 'Process Date
rs.Fields(6) = Data(5) 'Good Checks Singles
rs.Fields(7) = Data(6) 'Good Checks Stubs
rs.Fields(8) = Data(7) 'Good Checks Partial
rs.Fields(9) = Data(8) 'Good Stubs Partial
rs.Fields(10) = Data(9) 'Good Checks Multi
rs.Fields(11) = Data(10) 'Good Stubs Multi
rs.Fields(12) = Data(11) 'Check Only Checks
rs.Fields(13) = Data(12) 'Check Only Stubs
rs.Fields(14) = Data(13) 'Check And List Stubs
rs.Fields(15) = Data(14) 'Check And List Checks
rs.Fields(16) = Data(15) 'Suspense Checks
rs.Fields(17) = Data(16) 'Suspense Stubs
rs.Fields(18) = Data(17) 'Correspondence
rs.Fields(19) = Data(18) 'Envelopes
rs.Fields(20) = Data(19) 'Express Mail
rs.Fields(21) = Data(20) 'Lookup Stubs
rs.Fields(22) = Data(21) 'Online Lookups
rs.Fields(23) = Data(22) 'Stubs Only
rs.Fields(24) = Data(23) 'StopFile Hits
rs.Fields(25) = Data(24) 'SLC
rs.Fields(26) = Data(25) 'SL2
rs.Fields(27) = Data(26) 'MICR Scanline
rs.Fields(28) = Data(27) 'KeyStrokes Correspondence
rs.Fields(29) = Data(28) 'Keystrokes SLC
rs.Fields(30) = Data(29) 'Keystrokes SL2
rs.Fields(39) = Data(38) 'Exception Queue
rs.Fields(40) = Data(39) 'COA Queue


'IMAGE ARCHIVE ITEMS
rs.Fields(41) = Data(40) 'Image Archive Reports
rs.Fields(42) = Data(41) 'Image Archive Storage

'BATCHES PROCESSED
rs.Fields(43) = Data(42) 'Batches Processed

'ARC ITEMS
rs.Fields(44) = Data(43) 'Low Speed ARC Attempts
rs.Fields(45) = Data(44) 'High Speed ARC Attempts
rs.Fields(46) = Data(45) 'Low Speed ARC Converted
rs.Fields(47) = Data(46) 'High Speed ARC Converted

'ICL ITEMS
rs.Fields(48) = Data(47) 'ICL Attempts
rs.Fields(49) = Data(48) 'ICL Converted
rs.Fields(50) = Data(49) 'ICL Non-Conforming images

'RETURN ITEMS
rs.Fields(51) = Data(50) 'Auto Returns
rs.Fields(52) = Data(51) 'Returned Items

'POSITIVE POST (CDM) ITEMS
rs.Fields(53) = Data(52) 'Positive Post Checks
rs.Fields(54) = Data(53) 'Positive Post Stubs

'REMOTE PAYMENT CAPTURE ITEMS
rs.Fields(55) = Data(54) 'Remote Payment Single Checks
rs.Fields(56) = Data(55) 'Remote Payment Single Stubs
rs.Fields(57) = Data(56) 'Remote Payment Multi Checks
rs.Fields(58) = Data(57) 'Remote Payment Multi Stubs
rs.Fields(59) = Data(58) 'Remote Payment Check Only Checks
rs.Fields(60) = Data(59) 'Remote Payment Check Only Stubs
rs.Fields(61) = Data(60) 'Remote Payment Check & List Checks
rs.Fields(62) = Data(61) 'Remote Payment Check & List Stubs

'DOLLAR DEPOSTIED & PP CORRO REJECT ITEMS
rs.Fields(71) = Data(70) 'Dollars Deposited
rs.Fields(72) = Data(71) 'PP Corro Rejects



rs.Update
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

Posted on Sep 09, 2009

Ad

1 Suggested Answer

6ya6ya

6ya staff

  • 2 Answers

SOURCE:

Hi there,
Save hours of searching online or wasting money on unnecessary repairs by talking to a 6YA Expert who can help you resolve this issue over the phone in a minute or two.

Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

Here's a link to this great service

Good luck!

Posted on Jan 02, 2017

Ad

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Properties in importing excel table to an access table


This article explains how to import a Microsoft Excel file into Microsoft Access version 2.0 or 7.0. Microsoft Access can import files from Microsoft Excel version 2.0 through version 7.0. However, Microsoft Access cannot import Microsoft Excel 4.0 workbooks (.xlw). This article assumes that the Microsoft Excel file is using standard database formatting. That is:

To import a Microsoft Excel file in Microsoft Access 2.0 loadTOCNode(2, 'moreinformation');
  1. Save the file in Microsoft Excel using standard database formatting listed above. Close the file.
  2. Start Microsoft Access 2.0 and open the database you want to import the Microsoft Excel information into.
  3. On the File menu, click Import. From the Import dialog box, choose the appropriate version of Microsoft Excel that you are importing and click the OK button. Microsoft Excel 7.0 files are not on this list. If you are importing a version 7.0 file, choose the Microsoft Excel 5.0 option.
At this point options such as whether to append to an existing table or create a new table may be selected.
To import a Microsoft Excel file in Microsoft Access 7.0 loadTOCNode(2, 'moreinformation');
  1. Save the file in Microsoft Excel using standard database formatting listed above. Close the file.
  2. Start Microsoft Access 7.0 and open the database you want to import the Microsoft Excel information into.
  3. On the File menu, click Get External Data and then click Import.
  4. From the import dialog box, choose Microsoft Excel (*.xls) for the Files Of Type option.

    This will display only the Microsoft Excel files.
  5. Find the desired file and click the Import button.
The Import Spreadsheet Wizard appears and allows you to choose options specific to the file.

or you just use the link for any problem where you can get details of the importing
http://www.techrepublic.com/article/techniques-for-successfully-importing-excel-data-into-access/5276622

May 05, 2011 | Computers & Internet

1 Answer

Want to import email addresses into MS Outlook


Hey! follow the steps to get your job done


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.



Good luck :-)

May 27, 2010 | Microsoft Windows XP Home Edition

1 Answer

How to convert vba macros to visual studio?


  1. Clean your code. There are built in Application specific functions which don't work in VB Studio. Some will require you pre-load the application first, others just don't work at all.
  2. Save the VBA module as a BAS file. This is just a text file with the module code in it.
  3. Export your forms to file.
  4. Open Visual Studio
  5. Import your forms and modules
  6. Clean it up.
  7. Compile it

Apr 28, 2010 | Microsoft Visual Studio 2008 Professional...

2 Answers

VBA and macros


SLA consultants in India. The training program is intelligently designed by industry experts to provide hands on experience and in-depth knowledge to the participant regarding MA Access database and Visual Basic for application programming. The training will use real time practical sessions and live projects and assignments to offer you a better understanding of both the tools. https://www.slaconsultantsindia.com/vba-macros-programming.aspx

19b8b809-5a82-4606-8ac9-dd7129af6e3d.jpg

Jun 22, 2009 | Microsoft OFFICE 2003 BASIC OEM ENGLISH MS...

7 Answers

Undelete records from access table


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

Apr 17, 2009 | Microsoft Access 2000 (077-01277) for PC

1 Answer

Why does it say you have to install a 1m sub


ok heres the only sollution i could figure and it seemed to do just fine download leaf green rom view the text files and copy the 1m from leaf green over to the text files of fire red

Mar 16, 2009 | Video Game Consoles & Games

8 Answers

I can't see text in dialogue box of messenger mine or contacts


hey i too had da same prob and i jus downloaded a program called script from microsoft da name is script 5.7 jus download tht and install to solve this problem

Jan 26, 2009 | Yahoo Messenger

1 Answer

Need Formula


TEXT CASE
Convert to UPPER, Proper or lower
Auto Convert to UPPER, Proper or lower
Stop VBA Being Case Sensitive

SELECT CASE VBA
Select Case Statement. Great alternative to Else If Statements

CONVERSION
VBA: CBool(),CByt(),CCur(),CDate(),CDbl()Double,CDec(),CInt(),CLng(),CSng(),CStr(),CVar().
Example
dDate=cDate("25-Jan-07")
Convert Numbers to Words
Convert Numbers to Dollar Amount in Words
Spreadsheet Converter. Versions: HTML/JavaScript,Excel ASP.Net and Excel Java/JSP. $
SQL Database Migration $
Converters. Database Converters, Spreadsheet Converters, Text Converters, E-mail Conversion, Document Conversion, PDF Conversion and more! $
CSV Converter $

IMPORTING
Import SQL Into Excel-SQL Tester $
Import Excel Into Access $

CONDITIONAL FORMATTING
Conditional Formatting
More Than 3 Conditional Formats
Conditional Format Fonts for greater than 3

CUSTOM FORMATS
Custom Formats
Excel see a cells format as having four Sections. These are, from left to right: Positives;Negatives;Zeros;Text.

To hide zeros cell-by-cell use a Custom Number Format like 0.00;-0.00; where 0.00 is desired format for non zeros. Note the use of -0.00 for negatives.

To hide zeros on the Workbook level go to Tools>Options>View - Zero Values.

Dec 15, 2008 | Microsoft Windows XP Home Edition

2 Answers

Steps in saving file in excel to database


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

Dec 11, 2008 | Microsoft Computers & Internet

Not finding what you are looking for?
Microsoft Access 2003 for PC Logo

199 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

Ekse

Level 3 Expert

13434 Answers

Alun Cox

Level 3 Expert

2679 Answers

Peter Dragonetti

Level 3 Expert

798 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...