Discussion :

[ANOVA]

Bonjour,

j'ai 3 fichiers :

• Un fichier de calcul (fichier principal)
• Deux fichiers de données (qui constituent ma base, même si en vérité j'en ai des centaines) : DataFile1, DataFile2

L'organisation des deux fichiers de données est strictement identique de façon à ce qu'une même requête écrite dans le fichier de calcul puisse aller chercher les données indifféremment dans l'un ou l'autre fichier juste en changeant le datasource du RecordSet.

Seulement, selon le fichier, les données contenues dans les 8 premières lignes peuvent varier ce qui peut poser des problèmes puisque par défaut ADODB utilise les 8 premières lignes pour déterminer le type des données.

Voici un exemple.
Supposons que le DataFile1 soit organisé comme suit (8 premières lignes uniquement) :
Var1 Var2
20040512 #N/A N/A
20040810 #N/A N/A
20041109 #N/A N/A
20050428 1,8133
20050510 1,9733
20050819 2,1233
20051109 0
20060406 2,4667
Et que le fichier DataFile2 soit organisé comme suit (8 premières lignes uniquement) :
Var1 Var2
#N/A N/A 0,206
#N/A N/A 0,1993
#N/A N/A 0,1793
#N/A N/A 0,1583
#N/A N/A 0,1449
0 0,1392
#N/A N/A 0,1445
#N/A N/A 0,1506

Si je fais un :

SELECT * FROM Base
(NB : chaque fichier a une plage nommée Base qui contient les 8 premières lignes ci-dessus)
avec un RecordSet dont le DataSource est le DataFile1, alors la Var1 sera numérique (car c'est le type majoritaire sur les 8 premières lignes).

Si j'exécute la même requête avec un RecordSet dont le DataSource est le DataFile2, alors la Var1 sera alphanumérique.

Problème : dans ma requête, je souhaite mettre un WHERE et selon la nature des données (numérique/alphanumérique), la façon d'écrire la condition n'est pas la même.

Ma question est : Peut-on forcer le type (numérique/alphanumérique) d'une variable lorsqu'on fait une requête SQL en ADODB ?

Je vous remercie

[Mr Poulpe]

Bonjour,

Normalement, il y a une fonctionnalité pour forcer le pilote ADODB à forcer le scan de l'ensemble des données, et pas juste les 8 premières lignes. Malheureusement, cette fonctionnalité est buggée sur excel.

Pour forcer l'import en alpha numérique, tu peux utiliser l'option IMEX = 1 dans ta chaîne de connection.
dans ta connection string (tu devrais avoir un truc qui ressemble à ça, sauf que là c'est OLEDB

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

NB: Ca s'applique à l'ensemble des champs. Si tu as des champs numérique, je te conseille de les convertir après extraction.

CF aussi
http://support.microsoft.com/kb/257819/fr

Cordialement,

Poulpe

[Invité]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT IIf(IsDate([Champ1]) And InStr([Champ1],'/')<>0,Format([Champ1],'yyyy-mm-dd'),IIf(IsNumeric([Champ1]),Format([Champ1],'0.000000'),[Champ1])) AS Expr1
FROM

NB: Ca s'applique à l'ensemble des champs. Si tu as des champs numérique, je te conseille de les convertir après extraction.

pourquoi veux tu reformater les données après importation?

[Mr Poulpe]

Parce que dans ma solution, l'ensemble des champs est converti en texte. S'il y a des calculs à faire ensuite, il faut donc reconvertir les données en numérique.

[Invité]

Ok mais si tu regarde ma solution la conversion se fait dans la requête à la volé! c'est toujours gainant de parcourir le tableau Excel pour caler le format alors qu'il est possible de le faire dans la requête!

[ANOVA]

Merci à tous les deux, je progresse dans ce que je souhaite faire.

J'avais bien pensé à la conversion après l'extraction, mais je me disais bien qu'il y avait quelque chose de plus propre (et certainement meilleur d'un point de vue temps de calcul, or ça peut être un paramètre qui a son importance pour ce que je cherche à faire).

Je suis parti sur la solution proposée par RDurupt. Tout fonctionne très bien dès lors que je n'utilise pas de WHERE. En revanche, j'ai encore quelques questions sur la rédaction du WHERE (car c'est bien pour pouvoir écrire une telle condition que je fais tout ça). J'utilise ce code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
    'Requête
    monRecordSet.Open "SELECT Cstr([Var1]) AS Expr1, Cstr(Var2) AS Expr2 FROM [Base] WHERE NOT(Var1 IS NULL) AND NOT(Cstr(Var1)='#N/A N/A') AND NOT(Var2='#N/A N/A')", , adOpenStatic, adLockOptimistic
 
    Dim tableau() As Variant
    tableau = monRecordSet.GetRows()

Qui est une adaptation de ce que tu as proposé RDurupt (j'utiliserai certainement le format date au final, mais en attendant de résoudre le problème du WHERE, je reste sur du String pour éviter une éventuelle interférence des problèmes).

Problème : lorsque j'essaye de mettre le contenu dans un tableau avec l'instruction GetRows, j'ai une erreur Utilisation incorrecte de Null.

Si je rajoute :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
    While monRecordSet.EOF = False
        monRecordSet.MoveNext
    Wend

juste après la requête SQL, je peux voir (dans la fenêtre variables locales monRecordSet/Fields/Item/Value) que mon RecordSet a bien les 32 enregistrements que je souhaite (la propriété Fields.Count = 32 dans la fenêtre variables locales), seulement quand j'arrive au dernier enregistrement, le code rentre dans le while et mets à nouveau Utilisation incorrecte de Null à la ligne suivante.

Autre question annexe : est-ce qu'on peut utiliser Expr1 et Expr2 dans le WHERE, ça semblerait logique, mais j'ai pas l'impression que ça fonctionne.

[Invité]

l'instruction cstr([Champ]) à pour but de convertir un champ en string sauf qu'il peut y avoir des champ = null!
et ce même dans un fichier Excel. en revanche les variable VBA n'accepte pas les valeurs Null d'ou le message d'erreur.
la bonne façon de convertir en string même pour les valeurs Null est de concaténer le champ avec une chêne vide

Code Exemple 1 :

Sélectionner tout - Visualiser dans une fenêtre à part

select '' & [Champ] as {Nom de Mon Champ]

revenons à mon exemple précédant.

Code Exemple 2 :

Sélectionner tout - Visualiser dans une fenêtre à part

 IIF(codition,SiVrai,SiFau) as [test]

j'avais utilisé le IIF pour formater les champs mais en réalité, le IIF replace un valeur par une autre na l'exemple du poste précédant, on substituait la valeur du champ par sa valeur formaté ou sa valeur en l'état.

Code Exemple 3 :

Sélectionner tout - Visualiser dans une fenêtre à part

select IIF('Chien'='Chat','A bon?','A Oui c''est différent!') as bisard

si je ramène ça a ton code

Code Exemple 4 :

Sélectionner tout - Visualiser dans une fenêtre à part

select IIF(Var2='#N/A N/A','',Var2) as Exp1

la je remplace les Null de Var2 par un chêne vide ''
maintenant oui théoriquement il es possible d'utiliser les étiquette dans la close where mais dans les langage SQL propriétaire pas toujours et Microsoft est quelque fois belliqueux!

Code Exmple 5 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select '' & IIF(Var2='#N/A N/A','',Var2) as Exp1 from Table1 where Exp1 <>'';

que faire si il n'est pas possible d'utiliser les étiquettes?
et bien on fait des requêtes imbriqué en d'autre terme une requête qui appel une sous requête.

Code sous requête :

Sélectionner tout - Visualiser dans une fenêtre à part

(select '' & IIF(Var2='#N/A N/A','',Var2) as Exp1 from Table1) as frm

note que la sous requête es encadré par des () et quelle ne dispose pas de point virgule le ; signale la fin de la requête il ne peut y en avoir qu'un.
(sous requête) as frm là je lui donne un nom comme pour une table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select frm.* from (select '' & IIF(Var2='#N/A N/A','',Var2) as Exp1 from Table1) as frm where frm.Exp1<>'';

[Invité]

j'ai oublier de préciser que la fonction replace fonction dans Sql et qu'il peur être intéressant de reprendre la mise en forme conditionnelle expliqué dans mon premier poste et de l'encapsuler dans un replace.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Sql="select frm.* from (select replace('' & IIf(IsDate([Champ1]) And InStr([Champ1],'/')<>0,Format([Champ1],'yyyy-mm-dd'),IIf(IsNumeric([Champ1]),Format([Champ1],'0.000000'),[Champ1])),'#N/A N/A','') AS Expr1  from Table1) as frm where frm.Exp1<>'';"

[ANOVA]

OK, je regarde ça. Je me disais juste que c'était bizarre car la propriété Fields.Count du RecordSet renvoyait le bon nombre de ligne renseigné. Ça veut dire que mon RecordSet ne compte pas les enregistrements NULL, même s'il les contient ?

[Invité]

ce sont des valeurs de champs qui sont null

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
dim txt as string
txt =rs!Champ1 'Champ1 pas null
txt =rs!Champ2 'Champ2 null Erreur
txt ="" & rs!Champ2 'Champ2 null pas d'erreur

les variables VBA n'acceptent pas des valeur null.
que tu affect tableau = monRecordSet.GetRows() tu affect peut être une valeur null mais ça suffi.

[ANOVA]

Bon, désolé, je rends les armes. Je crois que je suis obligé de vous mettre les fichiers pour vous demander la réponse directe. J'espère qu'après ça, j'arriverai à comprendre la réponse (normalement oui).

Je vous joins donc 3 fichiers à mettre dans le même répertoire. Le fichier Principal est celui qui interroge les deux autres DataFile1, DataFile2.

J'ai testé plein de choses. Pour voir le type des mes données, je stocke le résultat de la requête dans un tableau de Variant et je regarde le type qui ressort.

Ce que je voudrais, c'est une requete qui me renvoie les lignes où il y a des données, où la donnée n'est pas #N/A N/A quelle que soit la variable. En plus de cela, je voudrais que la variable laDate soit au format Date de VBA et que la variable Data soit au format Double.

Est-ce que quelqu'un voit comment faire ?

Je vous remercie d'avance

[ANOVA]

En particulier, je pensais y être presque lorsque ceci a fonctionné :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

"SELECT CDate(right(Cstr(laDate),2)& chr(47) & left(right(Cstr(laDate),4),2) & chr(47) & left(Cstr(laDate),4)) As maDate, Cstr(Data) As maData FROM [Base] WHERE laDate IS NOT NULL"

Quelle que soit la base choisie, ce code me renvoie des Date pour la variable laDate et des String pour la Variable Data. Là, je me suis dit, si je fais des requêtes imbriquées, étant donné que la variable Data est toujours de type String, je peux faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

"SELECT * FROM(SELECT CDate(right(Cstr(laDate),2)& chr(47) & left(right(Cstr(laDate),4),2) & chr(47) & left(Cstr(laDate),4)) As maDate, Cstr(Data) As maData FROM [Base] WHERE laDate IS NOT NULL) AS REQUETE1 WHERE REQUETE1.maData<>'#N/A N/A'"

pour éliminer les lignes où il n'y a pas de données disponibles. Mais même ça, ça ne fonctionne pas. La requête fonctionne, mais j'ai à nouveau une utilisation incorrecte de Null lorsque je veux stocker le résultat de la requête dans le tableau...

[Invité]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Sub ExempleCompletMinimal_Requete()
Dim tableau() As Variant
 tableau = RequeteTableau(ThisWorkbook.Path + "\DataFile1.xlsm")
  tableau = RequeteTableau(ThisWorkbook.Path + "\DataFile2.xlsm")
 
End Sub
Function RequeteTableau(Base As String)
    Dim connexion As ADODB.Connection
    Dim monRecordSet As ADODB.Recordset
    'Instanciation des variables
    Set connexion = New ADODB.Connection
    Set monRecordSet = New ADODB.Recordset
    'Paramètres de l'objet monRecordSet
   ' monRecordSet.CursorLocation = adUseServer
   ' monRecordSet.ActiveConnection = connexion
    'Requête
 
    'Paramètres de l'objet connexion
    connexion.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = " + Base + ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"""
    connexion.Open
    Dim Sql As String
    Sql = "SELECT cdate(Left('' & laDate,4) & '-' & Mid('' & laDate,5,2) & '-' &  "
    Sql = Sql & "Right('' & laDate,2) &  ' 00:00:00'),Format([Data],'0.00000') "
    Sql = Sql & "FROM [Base] "
    Sql = Sql & "WHERE ((('' & laDate)<>'#N/A N/A') AND (('' & [laDate])<>'')  "
    Sql = Sql & "AND (('' & [Data])<>'#N/A N/A' And ('' & [Data])<>''));"
    monRecordSet.Open Sql, connexion, adOpenStatic, adLockOptimistic
    'Dim tableau() As Variant
    'Range("A2").CopyFromRecordset monRecordSet
    RequeteTableau = monRecordSet.GetRows()
    'On ferme la connexion
    monRecordSet.Close
    connexion.Close
    Set monRecordSet = Nothing
    Set connexion = Nothing
End Function

[ANOVA]

Ok, super. Ça marche.

Quand je vois ta réponse je me dis que j'étais pas loin, mais bon c'est ça l'informatique : dès fois notre programme bogue juste à cause d'une virgule à la place d'un point virgule. Le petit détail qui change tout.

Bref, merci beaucoup pour ton aide et ton temps, mon problème est résolu (et j'aurai appris quelque chose dans l'histoire).