The following code is in UniBasic, and I'm trying to download some data from SB-Client. Obviously, the variable names aren't the actual ones or anything like that. As of now, it will select the Data I want, and write it to the Excel file. The issue is that all of the Data is in a single cell(A1 to be exact) and I need it to actually be in the form of an excel file(Each element being in a different cell). Here's the code that puts the data into the Excel File, and after that is an example of the data that's put into it.
INFORMATION.DOWNLOAD:
E_APPLICATION = "Excel"
E_TOPIC = "C:/ExcelFiles/File.xlsx"
E_ITEMNAME = "R1C1:R50C50"
E_TIMEOUT = 200
EXECUTE ("SELECT MYFILE CUSTOMERS AMOUNT_DUE AMOUNT_PAID SAMPLE 25")
READSELECT E_DATA THEN
END
Now the code above has successfully selected the information I want. It's in the form of a selected list, however. Using the data, I then proceed to do this:
CALL TU.DDE.CONNECT(E_APPLICATION, E_TOPIC, E_HANDLE, E_STATUS)
CALL TU.DDE.WRITE(E_HANDLE, E_ITEMNAME, E_TIMEOUT, E_DATA, E_STATUS2)
CALL TU.DDE.DISCONNECT(E_HANDLE, E_STATUS3)
Now, I've attempted to use READLIST however it throws an error(Obtained using CALL TU.DDE.GET.ERROR(E_HANDLE, E_STATUS4)) about SavesList.Map or something like that. I'm currently in the process of implementing a READNEXT for loop, however, I figured I'd post this question on here really quick prior to attempting that. It isn't looking promising at the moment so far either.
If you guys need more information, just let me know.
Edit: Here's a sample of the data that gets put inside the cell:
"Apple.Inc¦500.00¦100.00¦Verizon¦200.00¦100.00¦Straight Talk¦50000.00¦40000.00¦SpaceX¦1000.00¦1000.000"
^Obviously made up data^
Unfortunately I don't believe there's currently a native UniBasic tool for writing direct to .xlsx. However, writing to a .csv format is fairly accessible with UniBasic. I often do this and then just use Excel if I need to reformat the file to something else (eg .xslx).
Generally, if I'm outputting to csv then I'll build the variable where value marks delimit cells, and attribute marks delimit rows. Then before outputting my file, I'll then change the value marks to commas, and attribute marks to new line.
(In this case, I've skipped the value marks/commas step by simply delimiting with commas in the first place.)
Knowing that you've got three data columns per row, something like this will get your data into a desired format:
EXECUTE ("SELECT MYFILE CUSTOMERS AMOUNT_DUE AMOUNT_PAID SAMPLE 25")
CSV_DATA = ''
IX = 0
LOOP
READNEXT CUSTOMER ELSE EXIT ;* don't have any more data lines so exit loop
READNEXT AMOUNT_DUE ELSE AMOUNT_DUE = ''
READNEXT AMOUNT_PAID ELSE AMOUNT_PAID = ''
IX += 1
CSV_DATA<IX> = CUSTOMER :',': AMOUNT_DUE :',': AMOUNT_PAID
REPEAT
I'll then usually define something along the lines of this to get the data into the CSV format:
EQU NEWLINE TO CHAR(13):CHAR(10) ;* this is for Windows
SWAP AM WITH NEWLINE IN CSV_DATA
You can then use the rest of your code to write CSV_DATA to file.