Results 1 to 2 of 2
  1. 02-04-2014, 03:13 PM #1
    tcheck
    tcheck is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Nov 2010
    Posts
    131

    Importing Data with missing values

    Below is a text file that we need to import from a POS system but the layout is not conducive to a typical database import. 2500 series of numbers are in the second field of the table and I would like to add that account number to the second field for each row and I would like to add the date 4 the field of each line but not sure how I would accomplish this. Any thoughts. Thanks in advance.

    " "|"25000.....0793"
    " "|" "|"Name of city, R1"|"01/25/2014 17:31:43"|80079| 21.23|"BROCCOLI"| 0.00|"Client"|1
    " "|" "|""|""|""|""|"SALMON OSCAR"| 19.59|""|""
    " "|" "|""|""|""|""|"WATER"| 0.00|""|""
    " "|" "|""|""|""|""|"WILD RICE"| 0.00|""|""
    " "|" "|""|"01/25/2014 18:26:56"|30060| 26.02|"***AS APP***"| 0.00|"Server"|1
    " "|" "|""|""|""|""|"BREAD LOAF"| 0.95|""|""
    " "|" "|""|""|""|""|"BROCCOLI"| 0.00|""|""
    " "|" "|""|""|""|""|"FRENCH FRIES"| 0.00|""|""
    " "|" "|""|""|""|""|"GARLIC SIRLOIN"| 17.95|""|""
    " "|" "|""|""|""|""|"GLASS WINE BAR"| 0.00|""|""
    " "|" "|""|""|""|""|"GLS DOMANI PINOT GRIGIO"| 5.00|""|""
    " "|" "|""|""|""|""|"KETCHUP"| 0.00|""|""
    " "|" "|""|""|""|""|"MED"| 0.00|""|""
    " "|" "|""|""|""|""|"PINOT GRIGIO GLASS"| 0.00|""|""
    " "|" "|""|""|""|""|"WATER"| 0.00|""|""
    " "|" "|""|""|""|""|"WITH GLASS"| 0.00|""|""
    " "|" "|""|"01/26/2014 13:11:35"|40011| 78.31|"& O.J."| 0.00|"Server"|1
    " "|" "|""|""|""|""|"& PINEAPPLE"| 0.00|""|""
    " "|" "|""|""|""|""|"BATCH 1000 MUGCLUB FILL"| 5.25|""|""
    " "|" "|""|""|""|""|"BOTTEMLESS MIMOSA"| 15.98|""|""
    " "|" "|""|""|""|""|"SIGNATURE BLOODY MARY"| 8.50|""|""
    " "|" "|""|""|""|""|"SIGNATURE DRINKS"| 0.00|""|""
    " "|" "|""|""|""|""|"SUNDAY BRUNCH"| 41.85|""|""
    " "|" "|""|""|""|""|"THE NORTHERN SAMPLE"| 0.00|""|""
    " "|" "|""|""|""|""|"WITH SNIT"| 0.00|""|""
    " "|" "|""|""|""|""|"WYCLIFF BTMLS MIMOSA"| 0.00|""|""
    " "|" "|""|"01/26/2014 13:36:21"|60056| 30.24|"SUNDAY BRUNCH"| 27.90|"server"|1
    " "|" "|""|""|""|""|"WATER"| 0.00|""|""
    " "|" "|""|"01/27/2014 18:09:49"|80035| 16.69|"+ CHICKEN"| 0.00|"Server"|1
    " "|" "|""|""|""|""|"ADULT MAC-N-CHEESE PASTA"| 12.95|""|""
    " "|" "|""|""|""|""|"Toffee Cookie Sundae"| 2.45|""|""
    " "|" "|""|"01/27/2014 19:26:31"|60057| 75.08|"***W/ ENTREE***"| 0.00|"Server"|1
    " "|" "|""|""|""|""|"BAR TEQUILA"| 9.00|""|""
    " "|" "|""|""|""|""|"BATCH 1000 MUGCLUB FILL"| 5.25|""|""
    " "|" "|""|""|""|""|"BELGIAN IPA 25oz"| 0.50|""|""
    " "|" "|""|""|""|""|"BROAD AXE 20 OZ"| 0.00|""|""
    " "|" "|""|""|""|""|"CRAB/ARTICHOKE QUESADILLA"| 12.59|""|""
    " "|" "|""|""|""|""|"CUBAN SANDWICH"| 10.59|""|""
    " "|" "|""|""|""|""|"LOADED BAKED POT"| 1.69|""|""
    " "|" "|""|""|""|""|"MC 20oz TAP"| 11.00|""|""
    " "|" "|""|""|""|""|"MC 25oz TAP"| 5.00|""|""
    " "|" "|""|""|""|""|"MC TAP BEER"| 0.00|""|""
    " "|" "|""|""|""|""|"THE DUKE 20 OZ"| 0.00|""|""
    " "|" "|""|""|""|""|"VODKA STEAMED MUSSELS"| 12.95|""|""
    " "|" "|""|"01/28/2014 15:04:23"|40024| 44.38|"BEDDA CHEDDA BURGER"| 19.90|"New Client"|1
    " "|" "|""|""|""|""|"LOADED BAKED POT"| 1.69|""|""
    " "|" "|""|""|""|""|"LONG ISLAND TEA"| 6.25|""|""
    " "|" "|""|""|""|""|"MED"| 0.00|""|""
    " "|" "|""|""|""|""|"OVERLAKE"| 9.59|""|""
    " "|" "|""|""|""|""|"SAMPLE OF BEER"| 0.00|""|""
    " "|" "|""|""|""|""|"THE NORTHERN SAMPLE"| 0.00|""|""
    " "|" "|""|""|""|""|"WAFFLE FRIES"| 3.38|""|""
    " "|" "|""|""|""|""|"WATER"| 0.00|""|""
    " "|"25000.....0793 Total:"|" "|" "|" "| 291.95|" "|" "|" "|7
    " "|"25000.....0795"
    " "|" "|"City, R3"|"01/22/2014 13:58:41"|60009| 14.33|"BUFFALO CHICKEN WRAP"| 9.95|"New Client"|1
    " "|" "|""|""|""|""|"CHED"| 0.00|""|""
    " "|" "|""|""|""|""|"CHEESE"| 0.59|""|""
    " "|" "|""|""|""|""|"CRISPY CHICKEN"| 0.00|""|""
    " "|" "|""|""|""|""|"GC DIP"| 0.99|""|""
    " "|" "|""|""|""|""|"RANCH"| 0.00|""|""
    " "|" "|""|""|""|""|"WAFFLE FRIES"| 1.69|""|""
    " "|" "|""|""|""|""|"WATER"| 0.00|""|""
    " "|" "|""|"01/24/2014 16:23:45"|60043| 38.04|"***W/ ENTREE***"| 0.00|"Server"|1
    " "|" "|""|""|""|""|"ASIAN & SOUP"| 19.18|""|""
    " "|" "|""|""|""|""|"C-CHIX NOODLE"| 0.00|""|""
    " "|" "|""|""|""|""|"COFFEE"| 4.18|""|""
    " "|" "|""|""|""|""|"CUP FRENCH ONION"| 0.00|""|""
    " "|" "|""|""|""|""|"DIET COKE"| 2.79|""|""
    " "|" "|""|""|""|""|"FRENCH FRIES"| 0.00|""|""
    " "|" "|""|""|""|""|"KETCHUP"| 0.00|""|""


    " "|" "|""|""|""|""|"NO CHARGE L-SOUP COMBO"| 0.00|""|""
    " "|" "|""|""|""|""|"ROASTED PRIME RIB & SOUP"| 8.95|""|""
    " "|" "|""|"01/24/2014 19:57:49"|20070| 53.14|"***AS APP***"| 0.00|"Server"|1
    " "|" "|""|""|""|""|"ALFREDO WITH CHICKEN"| 11.95|""|""
    " "|" "|""|""|""|""|"ASPARAGUS"| 0.00|""|""
    " "|" "|""|""|""|""|"OPTION CAESAR"| 2.95|""|""
    " "|" "|""|""|""|""|"OPTION SALAD"| 2.95|""|""
    " "|" "|""|""|""|""|"RANCH"| 0.00|""|""
    " "|" "|""|""|""|""|"SIMPLY GRILLED SALMON"| 31.18|""|""
    " "|" "|""|""|""|""|"WAFFLE FRIES"| 0.00|""|""
    " "|" "|""|""|""|""|"WATER"| 0.00|""|""
    " "|" "|""|""|""|""|"WILD RICE"| 0.00|""|""
    " "|"25000.....0795 Total:"|" "|" "|" "| 105.51|" "|" "|" "|3
    " "|"29100.....0114"
    Reply With Quote Reply With Quote
  2. 02-04-2014, 03:28 PM #2
    June7's Avatar
    June7
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,954
    Options:

    1. Open the text file as a VBA object and read in one line at a time, parse the string into distinct data and do something, such as write to fields in table.

    2. Import to a table and run VBA procedure to update fields then delete the extraneous rows
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
    Reply With Quote Reply With Quote
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. Transferspread sheet importing some WRONG values! WHAT?!
    By qwerty1 in forum Import/Export Data
    Replies: 1
    Last Post: 06-06-2012, 01:32 PM
  2. Importing numeric fields with null values from XML
    By hcorvallis in forum Import/Export Data
    Replies: 1
    Last Post: 10-20-2011, 08:09 AM
  3. Importing Excel file. (2 non numeric values won't import)
    By Amerigo in forum Import/Export Data
    Replies: 3
    Last Post: 05-20-2011, 11:31 AM
  4. Defaults Values Missing After DB Move
    By cbuechler in forum Database Design
    Replies: 2
    Last Post: 02-15-2011, 08:33 AM
  5. Missing records when importing
    By marubal21 in forum Import/Export Data
    Replies: 2
    Last Post: 08-25-2010, 09:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Other Forums: Microsoft Office Forums