0

I am working on an iSeries platform. I have programs which will create a CSV for the data that I need, and an RPG program which will run a Java program to take the CSV data and insert it into a .xls template to create a new Excel spreadsheet (not .xlsx). The problem that I have is the data values in the CSV are in quotes and inserted into the XLS spreadsheet as text, which causes problems for my Date cells when it's all done. I get the "little green triangles" because Excel sees these as text entered into a Date cell.

I know I can use the DateValue() function, and it works great, but then the requirement would be that we delete the original column with the text data and I lose the reference for the DateValue() function. What I'm looking for (and I am still looking) is a way to load the text/string date value into the DateValue() function as it comes into the cell without needing to reference another cell, OR a way to permanently capture that value in a cell in a supplemental column so the original text column can be deleted. Original text column looks like "06/08/21" which has been updated to be "06/08/2021" to eliminate the "2-digit year" complaint from Excel. My supervisor is not opposed to putting the =DateValue() solution in play, but that requires a reference to the original cell (unless there's a way around that) and she wants the original deleted if we go that direction.

I would appreciate any help or direction on this. Including how I might ask this question better.

EDIT: Apparently I failed to communicate the issue clearly. The "customer" has a requirement that the values coming from the database be formatted properly in 5 columns. "Pure CSV" will not format correctly for dollar amounts in doing CL CPYTOIMPF from database file to the CSV. IF there's an alternative manner of getting data from the iSeries database into a CSV, or into an XLS file, I'm willing to read posts and articles to get it worked out.

3
  • 1
    Maybe you can preprocess the input file and remove the quotes from the values in the date column. Show some example input file in your question. Can you modify the program that generates the CSV file or one of the programs that process the CSV file? Please edit your question to answer, don't use comments for this purpose.
    – Bodo
    Jun 9, 2021 at 15:43
  • I don't really understand why you need the Java program and don't use pure CSV. Excel will auto-format fields when importing CSV. Cells that looks like containing dates will be automatically in date format.
    – harrymc
    Jun 9, 2021 at 18:57
  • how does the "the original text column " looks like? any sample? | it the date actually imported as text, try to apply =DATEVALUE() on it.
    – p._phidot_
    Jun 10, 2021 at 11:18

1 Answer 1

0

You should be able to do a search and replace all for the quote marks and then reformat the cell as a date. You can record it as a macro and have a button in your toolbar linked to it, or run it as part of your import.

eg:

   Sub Datestring()
' Datestring Macro
' Removes quote marks and reformats as date
With Selection
    .Replace What:="""", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    .NumberFormat = "[$-x-sysdate]dddd, mmmm dd, yyyy"
    .EntireColumn.AutoFit
End With
End Sub

Just highlight the column you want to convert and run the macro, or just do it manually each time.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .