Import from Excel Spreadsheet

Startbeitrag von Larry am 08.10.2010 03:47

Hi,

I need some help trying to import from excel spreadsheet file into hyperfile. I have had problems with DateTime fields. When I tried to import many many times it will only bring in 8 characters for the datetime field which becomes only the date characters. In the spreadsheet on the datatime field I put them in a custom format like yyyymmddhhmm. In the subtype and the parameters in Windev I do the same but when it imports it goes back to yyyymmdd. I have been putting the spreadsheet into a tab delimited text file.

Also, I would like to bring these files into the system but if it is a duplicate just modify the record. If it does not exist then add the record.

Also, when I get the spreadsheet sometimes the data is not in the same order. Is there a way to put the data by the header names?

Thanks in the future
Larry

Antworten:

Hi Larry

how do you import?
What code do you use?

Without this info, it's going to be hard to help you

Best regards

--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

More information on http://www.fabriceharari.com

On 08/10/2010 01:44, Larry wrote:
> Hi,
> I need some help trying to import from excel spreadsheet file into hyperfile. I have had problems with DateTime fields. When I tried to import many many times it will only bring in 8 characters for the datetime field which becomes only the date characters. In the spreadsheet on the datatime field I put them in a custom format like yyyymmddhhmm. In the subtype and the parameters in Windev I do the same but when it imports it goes back to yyyymmdd. I have been putting the spreadsheet into a tab delimited text file.
> Also, I would like to bring these files into the system but if it is a duplicate just modify the record. If it does not exist then add the record.
> Also, when I get the spreadsheet sometimes the data is not in the same order. Is there a way to put the data by the header names?
> Thanks in the future
> Larry
>
>
Message forwarded from pcsoft.us.windev

von Fabrice Harari.pcs.crosspost - am 08.10.2010 09:50
Larry,

As Fabrice rightly states - some idea of what you've done so far would be very useful...

In general, my "Windev Solution" article shows the basic techniques:
http://forum.mysnip.de/read.php?27161,22800

I accept that my solution example doesn't include a Date example, but remember, the Result of calling the XLSData function is a string, so it may be that you need to manipulate the date before writing it to the DB?

Also, I can't help but notice you posted this in the Windev Solutions section as well for some reason :confused: Hitting all the forums with you request won't get your answer any faster [[5]]

Also see:
http://doc.pcsoft.fr/en%2DUS/?3080009&name=functions-for-managing-the-xls-files

HTH...

von DarrenF - am 08.10.2010 13:13
Fabrice,

I am using a Button to do an import. If the data is a string I do not have a problem. When I am importing a dateTime from a excel spreadsheet that is where I have my problem.

Client.Name = xlsdata(IDXLSFile,glocalInv_Row,1)
Client.ModDateTime = xlsdata(IDXLSFile,glocalinv_Row,2)

For 10/15/2010 11:38 I get this 20/--/0010 10:00:00

I know I must be doing something wrong.

Thanks again
Larry

von Larry - am 12.10.2010 06:04
Hello Larry,

as I said in my last post, use LOCAL VAR.

LOCAL
l_sValue is string
l_dtDate is DateTime

l_sValue = xlsdata(IDXLSFile,glocalinv_Row,2)

l_dtDate..Date = StringToDate( l_sValue[[1 A 10]] , "MM/DD/YYYY" )
l_dtDate..Time = Rigth(l_sValue, 5)

Client.Name = xlsdata(IDXLSFile,glocalInv_Row,1)
Client.ModDateTime = l_dtDate

Believe me, is the best way!

Best regards,

Jean-Yves

Message forwarded from pcsoft.us.windev

von Jean-Yves LIXON.pcs.crosspost - am 12.10.2010 07:43
Jean-Yves,

I did try your way and this is what is happening.

l_sValue brings in the 8 characters which is "dd/mm/yyyy" which is great "28/09/2010"

then

when I try getting the time which is

right(l_sValue,5) the data it grabs is /yyyy which is "/2010"

I did use the example that you gave me but looking through the debugger that is how I know what is taken place.

Thanks for the help.

Larry



von Larry - am 12.10.2010 17:38
Hi Larry,

In my explanation I am based on your post of 12 October, in which you REPORTED your problem with your example: 10/15/2010 11:38

l_sValue = xlsdata(IDXLSFile,glocalinv_Row,2)

l_dtDate..Date = StringToDate( l_sValue[[1 A 10]] , "MM/DD/YYYY" ) // Return 15/10/2010
l_dtDate..Time = Rigth(l_sValue, 5) // Return 11:38

This code is right, because in your example l_sValue = 10/15/2010 11:38 , you have a DATE and you have a TIME.

In your last post you have only a DATE.

to solve your problem, I suggest you complete my first code by a test on the length of the varible l_sValue. if size is above 10 then you a DATEand TIME else you have only a DATE

_sValue = xlsdata(IDXLSFile,glocalinv_Row,2)

l_dtDate..Date = StringToDate( l_sValue[[1 A 10]] , "MM/DD/YYYY" )
IF length(l_sValue) > 10 THEN l_dtDate..Time = Rigth(l_sValue, 5)

von Jean-Yves LIXON - am 12.10.2010 21:23
Larry,

I want to complete my previous post in offering you the idea of a function:
Your Datas:
Client.Name = xlsdata(IDXLSFile,glocalInv_Row,1)
Client.ModDateTime = DateFromExcel( xlsdata(IDXLSFile,glocalinv_Row,2) )

FUNCTION DateFromExcel( p_sValue )
LOCAL
l_dtDate is DateTime
l_sTheDate is String = p_sValue[[1 TO 10]]
l_sTheTime is String = p_sValue[[11 TO]]

l_dtDate..Date = StringToDate( l_sTheDate, "MM/DD/YYYY" )

IF l_sTheTime "" THEN
l_dtDate..Time = l_sTheTime
ELSE //

von Jean-Yves LIXON - am 12.10.2010 21:54
Jean-Yves,


I tried a little simple test just hardcoding the datetime just to see if it would work properly which it did not.

Here is what I did.


LOCAL

lsValue is string
ldtDate is Date
ldtTime is Time
ldtTotDate is DateTime

lsValue = ("10/15/2010 08:45")
ldtDate = StringToDate(lsValue[[1 A 10]],"MM/DD/YYYY")
ldtTime = Right(lsValue,5)
ldtTotDate = ldtDate + ldtTime


When Processing

ldtToDate = 20101015080050000
ldtDate = 20101015
ldtTime = 08:450000
lsValue = "10/15/2010 08:45"


Why is the time change from 08:45 to 080050000?

Again I do appreciate your help

Thanks
Larry

von Larry - am 13.10.2010 03:08
In my code there is a small error, in effect, "08:45" is a string formatted for the time, the correct time is "0845"
So, you just have corrected your code like this:

ldtTime = Replace (Right (lsValue, 5), ":" "")

Message forwarded from pcsoft.us.windev

von Jean-Yves LIXON.pcs.crosspost - am 13.10.2010 08:48
This is what I needed.

Again I do appreciate the help.

Thanks
Larry

von Larry - am 13.10.2010 12:12
On the excel spreadsheet the field is in Date format as 6/23/10 13:49

When I look at the sValue in debug mode I ONLY see "23/06/2010" and StringToDate which I get "20102306"

the data is "23/06/2010" but it does not bring in the time data from excel

Excel
Format = Date Example 6/23/10 13:49

Windev
sValue is string
dtDate is DateTime

sValue = xlsData(IDXLSFile,glocalinv_Row,13)

dtDate..Date = StringToDate( sValue[[1 A 10]] , "MM/DD/YYYY" )
dtDate..Time = Right(sValue, 5)

Again Thanks in advance
Larry

von Larry - am 19.10.2010 20:26
Hi Larry,

I did a test by formatting a Excel cell as a DateTime( 10/19/2010 09:45) and when i read it

sValue = xlsData( IDXLSFile, 1, 1 )

sValue is equals to "20101019094500000"

I think the best solution for you (for debug to) is to use this function:

Client.Name = DataFromExcel( xlsdata( IDXLSFile, glocalinv_Row, 1 ), xlsTypeColumn(IDXLSFile, 1, False ) )
Client.ModDateTime = DataFromExcel( xlsdata( IDXLSFile, glocalinv_Row, 2 ), xlsTypeColumn(IDXLSFile, 2, False ) )

//PROCEDURE DataFromExcel( p_Value, p_nType )
//LOCAL
// l_sValue is a string
// l_dValue is a date
// l_tValue is a time
// l_dtValue is a DateTime
// l_cValue is a currency
//
//SWITCH p_nType
// CASE xlsTypeString
// l_sValue = p_Value
// RESULT L_sValue
//
// CASE xlsTypeDate
// l_dValue = p_Value
// RESULT l_dValue
//
// CASE xlsTypeTime
// l_tValue = p_Value
// RESULT l_tValue
//
// CASE xlsTypeDateTime
// l_dtValue = p_Value
// RESULT l_dtValue
//
// CASE xlsTypeDouble
// RESULT p_Value
//
// CASE xlsTypeCurrency
// l_cValue = p_Value
// RESULT l_cValue
//
// CASE xlsTypeEmpty
// RESULT ""
//
// OTHER CASE
// Error("Undefined type")
// RESULT ""
//END


Attention:

xlsTypeColumn() should be used only if all datas of the column have the same format.

If you have different format in the same column, use the type var. xlsDocument

My_xlsDoc is a xlsDocument
My_xlsDoc = xlsOpen( "c:\User\Document\test.xls" )


Client.Name = DataFromExcel( My_xlsDoc[glocalinv_Row, 1], My_xlsDoc[glocalinv_Row, 1]..Type )
Client.ModDateTime = DataFromExcel( My_xlsDoc[glocalinv_Row, 2], My_xlsDoc[glocalinv_Row, 2]..Type )

Best regards,

Jean-Yves


von Jean-Yves LIXON - am 20.10.2010 06:20
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.