-
1 Attachment(s)
I think this is what you asked for.
-
Is the date the date you import thr data or from the file name?
-
From the file name. Each file is "NetCashMM.DD.YY.txt"
-
Ok, this may take a while.
-
Can you supply an actual original file for me to work with?
-
Yes, but it will not let me upload the txt file. I'll try again.
-
2 Attachment(s)
-
Thanks, I take it that you do not need the original VBA code import routine for customers etc that I posted previously?
-
-
no Way! I use that 10 times a day thanks to you.
-
OK, do you need data from the first line of the file which tells you the sales period?
But do you need the old code in with this code?
-
I dont need the data from the first line, that will be read from the file name. All the reports are 1 day reports. I compiled one ( i believe 2/20/20) for you so you had more categories showing the different possibilities that can show up.
I don't need the old code in with this one. If it imports the file with variable fields included, I believe I can manage from that point. The importing is what I can't figure out.
-
Yes, this one is not straightforward, the file format is not normal, ie it is not CSV or using a Space or Tab as a seperator.
So I need to work with the Table field names, the only problem is if they change them in the file output it may cause the code to crash.
Can you control the output or is that proprietary?
-
I control the output. So those field names will remain constant.
-
Can you control the type of output?
As it would be easier if it was an actual csv or Tab delimited.
-
No unfortunately that is what I am left with. The only options it gives me is to export to HTML or CSV/TXT
-
That is OK, I have imported a couple of lines and it has seperated out the title and amount for me.
I just have to figure out waht it is using to do so.
-
I maybe able to use your original code, and instead of one report use a few and get all the information. I don't want to take up your time if the way I am trying now is a huge pain. Let me see if I can get two or three reports and get all the data I need. The reports are in CSV form. Can I get back to you please?
-
I can do it with three reports using your original code! Thank you so much. It will be much better than trying to conform the annoying report. I seriously appreciate everything you do. Thank you .
-
It is not a problem, but for your information the file uses Horizontal Tab as the delimiter.
So if you want me to do this I can it is just a case of matching the Field names.
I could do a couple and you could do the rest.
-
I think I'll be fine this way, thank you again. I do have one question though. The code you provided before had me importing all the Fields on the report. If the report I want to use has 51 fields, which will remain constant, but I only want to import select ones... how could I modify the code? For example, the daily report I am pulling from has 51 fields, but I only need 19 of those fields. I could just import everything, but I have read how efficiency in these DB are key, so I figured it would be smart to limit to what I absolutely need.
Thank you!
-
DISREGARD LAST QUESTION PLEASE!
Actually, I asked that question incorrectly. Each report has 2 fields, but has 51 rows of Data. If I only want specific rows of data, do I manipulate that with the "If recount>0" statement? Or is there a more efficient way to choose which lines of data to import.
-
2 Attachment(s)
Here are the two reports that have all the info I need, and are constant. Department totals, I just take everything. Gross Sales, I only need select data....
Gross Sales (row 2)
Sales Tax 1 charged (5)
Sales Tax 2 Charged (8)
Bottle Deposit (23)
Bottle Returns (24)
Coupons (25)
Gift Card Credit (26)
Gift Card Cashout (27)
Gift Card Tendered (28)
Loyalty Credit (29)
Loyalty Redeemed (30)
Online Paidout (31)
Instant Paidout (32)
ACH Wholesale (33)
Checks Tendered (34)
Charge Tendered (35)
Credit Card Charges (36)
Debit Card Charges (37)
ROA Charge (40)
Obviously I wouldn't ask you to do this whole thing... but if you could suggest how to start, I can follow after. Thank you.
-
It depends on where the records are that you want, if they not at the beginning of the data you can set the recount> to where you want them to start.
ie
if recount>1 then
will start on line 2.
However if you wish to ignore later lines you will need an "If/then" for each line or batch of lines.
ie
if recount <> 3 and recount<> 4 and recount<>9 and recount<> 11 then
would skip those lines of code, you would then have to add an end if before the other end if for the recount>1.
However having complicated If then statements might actually sow down the import.
It might be better to leave it as is and just ignore or delete the data not required.
-
Gotcha... that makes sense. One more thought... if you look at the text files I sent, the data in the first column will be constant and actually probably should be fields in a table. Would it be better to import with the first column as the field and the second as the data for the table? That would eliminate half the data in the table each day. Also, if that was a better idea, how would I manipulate the code to import that instead?
-
That was basically how I think your table was setup, you then have to use if then statements to match the first column with the fieldname you want to put the value in.
ie
if varsplit(0) = "Beer then
![Beer] = varsplit(1)
end if
